VBA to print up to last row of specific column

[icantthink]

Hello,

I'm new at using VBA and would like to get some help. Let's say I have:

• Columns A:E
• Rows 1-5 are used as headers. I have this set so these headers print out on every page
• Below these rows, I have maybe 100 rows for data entry
• Column A is being used to name the row
• Columns B:D have will be filled out with data, just a number
• Column E will have formulas that will correspond to what is filled out in B:D (it is set up so if it is a zero value, the cell is blank)
• The very last row, let's say Row 101, has a formula to basically add up what is in each of those columns.

I'm looking for VBA code, so when I print, it prints only up to the last row of data that is in Column E. Which I know can be done. However, I still need to print the very last row, in this case Row 101, which also holds data in that Row for Column E. Is this possible?

 

[MARK858]

The way I am reading your request it would possibly just be...

ActiveSheet.PageSetup.PrintArea = Range("A1:E" & Range("E" & Rows.Count).End(xlUp).Row).Address
ActiveSheet.PrintOut

I am assuming with the above you mean all 5 columns up to the last row in column E

it prints only up to the last row of data that is in Column E.

 

[icantthink]

Hi, thanks for the reply. Below is a simplified screenshot of what I'm working with. Hopefully, it's a bit more clear with a picture.
In the example below, I'm looking for a VBA to print only rows 6-22 that have a value in column M. So using the below example, I only want to print up to row 16, with rows 17-22 collapsed (I'm not sure if that's the right word) and then continue printing the summary row below it (Rows 23 & 34) after the last row with data in it, in this case Row 16.



Excel 2013 32 bit

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2
3		L-101					L-102					Lot Size:	52,000	52,000	Page:	1
4	Customer	Box B	Box C	Box D	Box E	Box F	Box G	Box H	Box I	Box J	Box K	Box L	Total	Balance
5															By	Date
6	MO	10											120	51,880
7	MO	10											120	51,760
8	MO	10											120	51,640
9	MO	10											120	51,520
10	MO	10											120	51,400
11	MO	100											1,200	50,200
12	MO	100											1,200	49,000
13	MO	1,000											12,000	37,000
14	MO	1,000											12,000	25,000
15	MO	1,000											12,000	13,000
16	MO	1,000											12,000	1,000
17
18
19
20
21
22
23	TOTAL	51,000	0	0	0	0	0	0	0	0	0	0	51,000	1,000
24	Calculation of Yield:	98.1%

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula M6 =IF(((B6*12)+(C6*24)+(D6*50)+(E6*12)+(F6*24)+(G6*12)+(H6*24)+(I6*50)+(J6*12)+(K6*24)+L6)=0,"",((B6*12)+(C6*24)+(D6*50)+(E6*12)+(F6*24)+(G6*12)+(H6*24)+(I6*50)+(J6*12)+(K6*24)+L6)) N6 =IF(M6="","",N3-M6) M7 =IF(((B7*12)+(C7*24)+(D7*50)+(E7*12)+(F7*24)+(G7*12)+(H7*24)+(I7*50)+(J7*12)+(K7*24)+L7)=0,"",((B7*12)+(C7*24)+(D7*50)+(E7*12)+(F7*24)+(G7*12)+(H7*24)+(I7*50)+(J7*12)+(K7*24)+L7)) N7 =IF(M7="","",N6-M7) M8 =IF(((B8*12)+(C8*24)+(D8*50)+(E8*12)+(F8*24)+(G8*12)+(H8*24)+(I8*50)+(J8*12)+(K8*24)+L8)=0,"",((B8*12)+(C8*24)+(D8*50)+(E8*12)+(F8*24)+(G8*12)+(H8*24)+(I8*50)+(J8*12)+(K8*24)+L8)) N8 =IF(M8="","",N7-M8) M9 =IF(((B9*12)+(C9*24)+(D9*50)+(E9*12)+(F9*24)+(G9*12)+(H9*24)+(I9*50)+(J9*12)+(K9*24)+L9)=0,"",((B9*12)+(C9*24)+(D9*50)+(E9*12)+(F9*24)+(G9*12)+(H9*24)+(I9*50)+(J9*12)+(K9*24)+L9)) N9 =IF(M9="","",N8-M9) M10 =IF(((B10*12)+(C10*24)+(D10*50)+(E10*12)+(F10*24)+(G10*12)+(H10*24)+(I10*50)+(J10*12)+(K10*24)+L10)=0,"",((B10*12)+(C10*24)+(D10*50)+(E10*12)+(F10*24)+(G10*12)+(H10*24)+(I10*50)+(J10*12)+(K10*24)+L10)) N10 =IF(M10="","",N9-M10) M11 =IF(((B11*12)+(C11*24)+(D11*50)+(E11*12)+(F11*24)+(G11*12)+(H11*24)+(I11*50)+(J11*12)+(K11*24)+L11)=0,"",((B11*12)+(C11*24)+(D11*50)+(E11*12)+(F11*24)+(G11*12)+(H11*24)+(I11*50)+(J11*12)+(K11*24)+L11)) N11 =IF(M11="","",N10-M11) M12 =IF(((B12*12)+(C12*24)+(D12*50)+(E12*12)+(F12*24)+(G12*12)+(H12*24)+(I12*50)+(J12*12)+(K12*24)+L12)=0,"",((B12*12)+(C12*24)+(D12*50)+(E12*12)+(F12*24)+(G12*12)+(H12*24)+(I12*50)+(J12*12)+(K12*24)+L12)) N12 =IF(M12="","",N11-M12) M13 =IF(((B13*12)+(C13*24)+(D13*50)+(E13*12)+(F13*24)+(G13*12)+(H13*24)+(I13*50)+(J13*12)+(K13*24)+L13)=0,"",((B13*12)+(C13*24)+(D13*50)+(E13*12)+(F13*24)+(G13*12)+(H13*24)+(I13*50)+(J13*12)+(K13*24)+L13)) N13 =IF(M13="","",N12-M13) M14 =IF(((B14*12)+(C14*24)+(D14*50)+(E14*12)+(F14*24)+(G14*12)+(H14*24)+(I14*50)+(J14*12)+(K14*24)+L14)=0,"",((B14*12)+(C14*24)+(D14*50)+(E14*12)+(F14*24)+(G14*12)+(H14*24)+(I14*50)+(J14*12)+(K14*24)+L14)) N14 =IF(M14="","",N13-M14) M15 =IF(((B15*12)+(C15*24)+(D15*50)+(E15*12)+(F15*24)+(G15*12)+(H15*24)+(I15*50)+(J15*12)+(K15*24)+L15)=0,"",((B15*12)+(C15*24)+(D15*50)+(E15*12)+(F15*24)+(G15*12)+(H15*24)+(I15*50)+(J15*12)+(K15*24)+L15)) N15 =IF(M15="","",N14-M15) M16 =IF(((B16*12)+(C16*24)+(D16*50)+(E16*12)+(F16*24)+(G16*12)+(H16*24)+(I16*50)+(J16*12)+(K16*24)+L16)=0,"",((B16*12)+(C16*24)+(D16*50)+(E16*12)+(F16*24)+(G16*12)+(H16*24)+(I16*50)+(J16*12)+(K16*24)+L16)) N16 =IF(M16="","",N15-M16) M22 =IF(((B22*12)+(C22*24)+(D22*50)+(E22*12)+(F22*24)+(G22*12)+(H22*24)+(I22*50)+(J22*12)+(K22*24)+L22)=0,"",((B22*12)+(C22*24)+(D22*50)+(E22*12)+(F22*24)+(G22*12)+(H22*24)+(I22*50)+(J22*12)+(K22*24)+L22)) N22 =IF(M22="","",#REF!-M22) B23 =SUM(B6:B22)*12 C23 =SUM(C6:C22)*24 D23 =SUM(D6:D22)*50 E23 =SUM(E6:E22)*12 F23 =SUM(F6:F22)*24 G23 =SUM(G6:G22)*12 H23 =SUM(H6:H22)*24 I23 =SUM(I6:I22)*50 J23 =SUM(J6:J22)*12 K23 =SUM(K6:K22)*24 L23 =SUM(L6:L22) M23 =IF(SUM(M6:M22)=0,"",(SUM(M6:M22))) N23 =IF(M23=0,"",(N3-M23)) B24 =(M23/M3) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[MARK858]

print only rows 6-22 that have a value in column M... rows 17-22 collapsed

Where did this column M come from? It isn't mentioned at all in the original post nor the "collapsing" of rows.

Hopefully, it's a bit more clear with a picture.

Would have been clearer if you had mentioned the above

Please confirm that you want (based on your sample).

a) Rows 17:22 hidden.
b) Print to the last row with a formula or text. Please note this is either column A or column B not column E. I am assuming you meant row 24 as per the image and not 34 as you put in the quote below

summary row below it (Rows 23 & 34)

c) Are columns O and P supposed to be in the print area? they haven't been mentioned.

Please think carefully before answering the above and include any other relevant details as the code will be written based on the answers to the above and I will probably be unwilling to do another rewrite of the code (doesn't include corrections based on the above).

 

[MARK858]

Just because I don't know when I will be online over the weekend below is my interpretation based on post #3 and all the answers to post # 4 being yes.

Please note the first preview is the result.

I didn't know how you wanted it after the hiding and so the code after the 1st preview is to reset the sheet.
Obviously delete the reset code if you don't need it and change the first PrintPreview to PrintOut if you are sure it is working correctly.

Sub zxxxx()
    Dim LstRw As Long, hRng As Range, hRow As Long, eRow As Long
    Dim mystate1 As String, mystate2 As Long, mystate3 As String
    Application.ScreenUpdating = False
    LstRw = Cells(Rows.Count, "A").End(xlUp).Row

    eRow = LstRw - 2
    Set hRng = Range("M1:M" & LstRw - 2)
    hRow = hRng.Find("*", , xlValues, , xlByRows, xlPrevious).Row + 1

    Rows(hRow & ":" & eRow).EntireRow.Hidden = True
    
    With ActiveSheet.PageSetup
        mystate1 = .Orientation
        mystate2 = .Zoom
        mystate3 = .PrintArea
    End With
    
    With ActiveSheet.PageSetup
        .PrintArea = Range("A1:P" & Range("A" & Rows.Count).End(xlUp).Row).Address
        .Orientation = xlLandscape
        .Zoom = False
        .FitToPagesWide = 1
    End With
    
    ActiveSheet.PrintPreview
    
    Rows(hRow & ":" & eRow).EntireRow.Hidden = False
    With ActiveSheet.PageSetup
        .Orientation = mystate1
        .Zoom = mystate2
        .PrintArea = mystate3
    End With
    
    ActiveSheet.PrintPreview
    
    Application.ScreenUpdating = True
End Sub

 

[icantthink]

Hi, thank you for the above! I was away on the weekend and managed to test it yesterday and today. The worksheet I'm working on is pretty much the same as the one I posted, only with about 100 or so rows for data entry, instead of the 17 or so rows in the picture above. This forum, wouldn't let me post the full worksheet for some reason, maybe a limit on the rows.

But, having said that, the code worked when I tried it out yesterday. I'm still trying to test it out by filling some of the cells in with data but today I keep getting a "Microsoft Visual Basic Run Time Error 1004" when I go to click 'Run, Macro'. Any ideas?

Also, I'm not sure what you mean by resetting the sheet after the first preview. Could you elaborate?

 

[MARK858]

a "Microsoft Visual Basic Run Time Error 1004" when I go to click 'Run, Macro'. Any ideas?

No as you haven't stated what is different to the sheet where "the code on worked when I tried it out yesterday" and what line it is erroring out on. Unless when you run the code the sheet is not the activesheet and the sheet that is active has less than the 3rd row used in column A, then you will get that error.
If the sheet is not going always going to be the activesheet then you need to supply the sheet name so the code can be adjusted (I can see that it is Sheet1 in the screenshot but need to know what the actual sheet name is going to be in reality).

Also, I'm not sure what you mean by resetting the sheet after the first preview. Could you elaborate?

Exactly what it says. The code alters the layout by setting to Landscape, hiding the rows and changing the print area. The last part of the code reverses these actions and puts the sheet back to the settings the sheet was on before the code was ran (you can see the result this has in the 2nd printpreview).
I should really have also reset the "FitToPagesWide" but didn't.

 

[icantthink]

The Run Time Error went away. I had multiple sheets open when testing the other day. I didn't realize that would cause an issue.
I see what you mean by resetting the sheet. I didn't realize that the code was actually altering the worksheet, I thought it was just selecting a print area. Appreciate the explanation.
Everything seems to work perfectly. Thank you! Thank you! ray:

 

[MARK858]

I had multiple sheets open when testing the other day. I didn't realize that would cause an issue.

Change the Workbook (in blue) and Worksheet (in red) name to your Workbooks (including the correct extension) and Worksheets name if the above is a possibility.

Sub zxxxx()
    Dim LstRw As Long, hRng As Range, hRow As Long
    Dim mystate1 As String, mystate2 As Long
    Dim mystate3 As String, myadd As String, eRow As Long

    Application.ScreenUpdating = False

    With Workbooks("[COLOR="#0000CD"]msht.xlsb[/COLOR]").Sheets("[COLOR="#FF0000"]Sheet1[/COLOR]")
        LstRw = .Cells(.Rows.Count, "A").End(xlUp).Row
        eRow = LstRw - 2
        Set hRng = .Range("M1:M" & LstRw - 2)
        hRow = hRng.Find("*", , xlValues, , xlByRows, xlPrevious).Row + 1

        .Rows(hRow & ":" & eRow).EntireRow.Hidden = True

        With .PageSetup
            mystate1 = .Orientation
            mystate2 = .Zoom
            mystate3 = .PrintArea
        End With

        myadd = .Range("A1:P" & .Range("A" & .Rows.Count).End(xlUp).Row).Address
        With .PageSetup
            .PrintArea = myadd
            .Orientation = xlLandscape
            .Zoom = False
            .FitToPagesWide = 1
        End With

        .PrintPreview

        .Rows(hRow & ":" & eRow).EntireRow.Hidden = False

        With .PageSetup
            .Orientation = mystate1
            .Zoom = mystate2
            .PrintArea = mystate3
        End With

        .PrintPreview
    End With

    Application.ScreenUpdating = True
End Sub

 

[icantthink]

Will do. Thanks again!