Excel vba code to print

Question

So with your guys help I made a macro that would read down a singular column and find the last cell that has data entered into it. I then wanted to add nested if then elseif statements that would tell it to print x number of pages based on when the last cell is listed. This is the code im using. What am I doing wrong?

    Sub LastRowInOneColumn()
    'Updateby20150305
    Dim xLastRow As Long
    With Application.ActiveSheet
        xLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
    End With
    MsgBox xLastRow

    If xLastRow = "22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,30,40,41,42,43,44,45" Then

    Application.Dialogs(xlDialogPrinterSetup).Show
    'Worksheets("Sheet1").PrintOut From:=1, To:=1, Preview:=True

    End If

End Sub

I am aware that my print command is in comments, i put that in there temporarily while i was working on something else. — Gehn47
I may have just figured it out, the sheet number is incorrectly labeled. — Gehn47

Scott Holtzman Scott Holtzman · Accepted Answer · 2017-10-25T16:20:44

I prefer Select Case with this types of scenarios. I also suggest explicitly stating which worksheet to consider, instead of ActiveSheet. If you have multiple sheets, you can pass the sheet into this sub in an argument and call from another sub.

Option Explicit

Sub LastRowInOneColumn()

    Dim MySheet As Worksheet
    Set MySheet = Worksheets("Sheet1")

    With MySheet

        Dim LastRow As Long
        LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

        Select Case True

            Case LastRow > 21 And LastRow < 46

                Application.Dialogs(xlDialogPrinterSetup).Show
                'Worksheets("Sheet1").PrintOut From:=1, To:=1, Preview:=True

        End Select

    End With

End Sub

Excel vba code to print

2 Answers