How to set variable range with some cells in columns blank?

[Jeddo]

I am trying to set a variable print range for retrieved data in which some of the cells may be blank. The range could be from one row up to 250 rows depending on the data retrieved. Columns A & B will not have any blank cells, but columns beyond C & D may have blank cells. As in the workbox below, I would need to set a print range of A1 to D17. I have a formula that will find the last row in column B, but I can't figure out how to set the range for two more columns to the right.

Range("A1", Range("B1").End(xlDown)).PrintOut

How do I set the print range to encompass columns C & D?

Print template.xlsx
	A	B	C	D
1		Sold By: Me
2
3		Name	Business Name	Phone #
4	1	John		555-1234
5	2	James
6	3	Paul	P&S Cattle Co.	555-1236
7	4	Henry		555-1237
8	5	George
9	6	Susan	Anytown Auto Parts	555-1239
10	7	Ginny		555-1240
11	8	Edith	Edith's Closet	555-1241
12	9	Beatrice		555-1242
13	10	Eric
14	11	Joe		555-1244
15	12	Shawn	Shawn's Landscaping	555-1245
16	13	Steven
17	14	Bobby
Sheet1

 

[bebo021999]

Range("A1:D" & Range("B1").End(xlDown).row).PrintOut

 

[Jeddo]

Range("A1:D" & Range("B1").End(xlDown).row).PrintOut

I can get the code to work for the example range it is set to, but when I try to modify it for the ranges I need, it doesn't work. I thought it would just be a matter of changing the columns in the formula, but it won't work.

Current range I am trying to set it up for is AZ6 to BC61. Changed the code to the following: Range("AZ6:BC" & Range("BA8").End(xlDown).row).PrintOut It prints the range AZ1 to BC8 instead of AZ6 to BC61, going UP from BA8 instead of down from BA8.

I'm missing something in the code when I try to set up a different range then the example I sent. I have several ranges like this I am trying to set up, so if I can get one to work and understand the syntax, I can get the rest. Any ideas?

 

[bebo021999]

debug.print Range("BA8").End(xlDown).row

what result is in immediate window? is it 61?

 

[Alex Blakenburg]

@Jeddo - how are you determing what block of columns to test ?

If you are hard coding the columns in the code then you could use something like this:

Sub LastRow()

    Dim sht As Worksheet
    Dim rng As Range
    
    Set sht = ActiveSheet                      ' <--- if not the activesheet put in the sheet
    Set rng = sht.Range("J:L")               ' <--- Change this to the desired columns
    
    Dim lastRow As Long, lastColumn As Long
    
    lastRow = rng.Find(What:="*" _
                , Lookat:=xlPart _
                , LookIn:=xlFormulas _
                , searchorder:=xlByRows _
                , searchdirection:=xlPrevious).Row
End Sub

 

[Jeddo]

debug.print Range("BA8").End(xlDown).row

what result is in immediate window? is it 61?

In the test I am trying, it goes down to row 44

 

[Jeddo]

@Jeddo - how are you determing what block of columns to test ?

If you are hard coding the columns in the code then you could use something like this:

Sub LastRow()

    Dim sht As Worksheet
    Dim rng As Range
   
    Set sht = ActiveSheet                      ' <--- if not the activesheet put in the sheet
    Set rng = sht.Range("J:L")               ' <--- Change this to the desired columns
   
    Dim lastRow As Long, lastColumn As Long
   
    lastRow = rng.Find(What:="*" _
                , Lookat:=xlPart _
                , LookIn:=xlFormulas _
                , searchorder:=xlByRows _
                , searchdirection:=xlPrevious).Row
End Sub

Please keep in mind I am a pathetic novice when it comes to VBA.

To your first question, I am hard coding the columns. I have several different ranges that collect data based upon what search I am doing. Each range has a specific set of columns, but only one of those columns is certain to have no blank cells between the data. The rows can run anywhere from 1 to 250. Didn't want to be printing out 6-7 pages of blank paper.

Tried your coding. I can get it to print the first two columns, with all of the desired data, but doesn't print any of the two columns to the right.
Changed the desired columns to "AZ:BC"
Changed Find(What:= to "BA6"
It printed out the desired rows of columns AZ and BA, but nothing for BB and BC
I was wanting it to print the range AZ6:BC44, but it printed AZ6: BA44

 

[Alex Blakenburg]

Which is why I asked how you are determining the columns. The code I gave you only gets you the last row for the range of columns you tell it to use. You still need to tell the print area what range you want it to use.

Try something like this:
(I am sure someone can give you a more compact version but start with this)

Sub testFindAndPrint()

    Dim sht As Worksheet
    Dim rng As Range
    Dim startCell As Range
    Dim lastRow As Long
    
    Set sht = ActiveSheet                      ' <--- if not the activesheet put in the sheet
    Set rng = sht.Range("AC:BC")               ' <--- Change this to the desired columns
    Set startCell = sht.Range("AC6")           ' <--- Change this to the desired top left corner of print range
    
    lastRow = rng.Find(What:="*" _
                , Lookat:=xlPart _
                , LookIn:=xlFormulas _
                , searchorder:=xlByRows _
                , searchdirection:=xlPrevious).Row

    startCell.Resize(lastRow - startCell.Row + 1, rng.Columns.Count).PrintOut
                
End Sub

 

[Jeddo]

Which is why I asked how you are determining the columns. The code I gave you only gets you the last row for the range of columns you tell it to use. You still need to tell the print area what range you want it to use.

Try something like this:
(I am sure someone can give you a more compact version but start with this)

Sub testFindAndPrint()

    Dim sht As Worksheet
    Dim rng As Range
    Dim startCell As Range
    Dim lastRow As Long
   
    Set sht = ActiveSheet                      ' <--- if not the activesheet put in the sheet
    Set rng = sht.Range("AC:BC")               ' <--- Change this to the desired columns
    Set startCell = sht.Range("AC6")           ' <--- Change this to the desired top left corner of print range
   
    lastRow = rng.Find(What:="*" _
                , Lookat:=xlPart _
                , LookIn:=xlFormulas _
                , searchorder:=xlByRows _
                , searchdirection:=xlPrevious).Row

    startCell.Resize(lastRow - startCell.Row + 1, rng.Columns.Count).PrintOut
               
End Sub

Getting an error message "Expecting End Sub". Didn't get this with your first code you sent me and can't figure out what the difference is that is causing it.
Here is my entire code, don't laugh too hard. Getting bits and pieces from google

Private Sub cmdPrintSoldBy_Click()

If MsgBox("Do you want to print out Ticket Sold By List?", vbQuestion + vbYesNo) <> vbYes Then
Exit Sub
End If

Copy_Sold_By

With Sheet1
    Application.PrintCommunication = False
    With .PageSetup
    
        .BottomMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0#)
        .TopMargin = Application.InchesToPoints(0.5)
        .HeaderMargin = Application.InchesToPoints(0#)
        .LeftMargin = Application.InchesToPoints(0.25)
        .RightMargin = Application.InchesToPoints(0.25)
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintArea = Sheet1.Range("AZ6:BC61").Address
        
    End With
    Application.PrintCommunication = True
   End With

Sub testFindAndPrint()

    Dim sht As Worksheet
    Dim rng As Range
    Dim startCell As Range
    Dim lastRow As Long
    
    Set sht = ActiveSheet                      ' <--- if not the activesheet put in the sheet
    Set rng = sht.Range("AZ:BC")               ' <--- Change this to the desired columns
    Set startCell = sht.Range("AZ6")           ' <--- Change this to the desired top left corner of print range
    
    lastRow = rng.Find(What:="BA6" _
                , Lookat:=xlPart _
                , LookIn:=xlFormulas _
                , searchorder:=xlByRows _
                , searchdirection:=xlPrevious).Row

    startCell.Resize(lastRow - startCell.Row + 1, rng.Columns.Count).PrintOut
                
End Sub

With Sheet1
    Application.PrintCommunication = False
    With .PageSetup
    
         .Zoom = 100
          
    End With
    Application.PrintCommunication = True
   End With

End Sub

 

[Alex Blakenburg]

Since you are copying the code into an existing Sub, you need to get rid of these lines:

Sub testFindAndPrint

and this line which is before the line, With Sheet1

End Sub

(so not the one right at the end)