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



## Jeddo (Apr 17, 2022)

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.xlsxABCD1Sold By:    Me23NameBusiness NamePhone #41John 555-123452James 63Paul P&S Cattle Co.555-123674Henry555-123785George96SusanAnytown Auto Parts555-1239107Ginny555-1240118EdithEdith's Closet555-1241129Beatrice555-12421310Eric1411Joe555-12441512ShawnShawn's Landscaping555-12451613Steven1714BobbySheet1


----------



## bebo021999 (Apr 17, 2022)

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


----------



## Jeddo (Apr 17, 2022)

bebo021999 said:


> ```
> 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 (Apr 17, 2022)

```
debug.print Range("BA8").End(xlDown).row
```
what result is in immediate window? is it 61?


----------



## Alex Blakenburg (Apr 18, 2022)

@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 (Apr 18, 2022)

bebo021999 said:


> ```
> 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 (Apr 18, 2022)

Alex Blakenburg said:


> @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:
> 
> ...


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 (Apr 18, 2022)

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 (Apr 18, 2022)

Alex Blakenburg said:


> 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)
> ...


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 (Apr 18, 2022)

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)


----------



## Jeddo (Apr 17, 2022)

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.xlsxABCD1Sold By:    Me23NameBusiness NamePhone #41John 555-123452James 63Paul P&S Cattle Co.555-123674Henry555-123785George96SusanAnytown Auto Parts555-1239107Ginny555-1240118EdithEdith's Closet555-1241129Beatrice555-12421310Eric1411Joe555-12441512ShawnShawn's Landscaping555-12451613Steven1714BobbySheet1


----------



## Alex Blakenburg (Apr 18, 2022)

Why do you have `rng.Find(What:="BA6"` ?
You should leave it as “*”


----------



## Jeddo (Apr 18, 2022)

Alex Blakenburg said:


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


Did that, but now I get a debug error and the following code is highlighted in yellow


```
lastRow = rng.Find(What:="BA6" _
                , Lookat:=xlPart _
                , LookIn:=xlFormulas _
                , searchorder:=xlByRows _
                , searchdirection:=xlPrevious).Row
```

FYI, the first code you sent me had the Sub and End Sub, but I didn't get any error running it.  Is it possible there is some kind of conflict with my existing code?


----------



## Jeddo (Apr 18, 2022)

Alex Blakenburg said:


> Why do you have `rng.Find(What:="BA6"` ?
> You should leave it as “*”


I see that now.  Changed it back, but still getting the same results with the highlighted text


----------



## Alex Blakenburg (Apr 18, 2022)

I have logged off for the night but try replacing what I gave you with this for consistency


```
Dim rng As Range
    Dim startCell As Range
    Dim lastRow As Long
   
    With Sheet1
       Set rng = .Range("AZ:BC")               ' <--- Change this to the desired columns
       Set startCell = .Range("AZ6")           ' <--- Change this to the desired top left corner of print range
    End With
   
    lastRow = rng.Find(What:="*" _
                , Lookat:=xlPart _
                , LookIn:=xlFormulas _
                , searchorder:=xlByRows _
                , searchdirection:=xlPrevious).Row

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


----------



## Jeddo (Apr 18, 2022)

Alex Blakenburg said:


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


I still can't get it to print the two right columns, BB and BC.  It prints to the last cell with data in BA column, but can't pick up the other two.  Played around with it for a couple of hours, but can't wrap my head around it.


----------



## Alex Blakenburg (Apr 18, 2022)

Please show me the full code you are now using.


----------



## Jeddo (Apr 19, 2022)

This is the code I am using on this particular range I want to print.  This range on rare occasion will require two pages to print, with only one or two rows on the second page.  I am trying to set it up to print everything on one page when that happens. It's crude, but I'm trying to learn.


```
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("AZ56:BC61").Address
        
    End With
    Application.PrintCommunication = True
   End With

Range("AZ6", Range("BA8").End(xlDown)).PrintOut

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


----------



## Alex Blakenburg (Apr 19, 2022)

Try this:
Note: I am using Find for the last row on the basis that you don't know which one of the columns is going to have the most rows.


```
Private Sub cmdPrintSoldBy_Click()

    If MsgBox("Do you want to print out Ticket Sold By List?", vbQuestion + vbYesNo) <> vbYes Then
        Exit Sub
    End If
    
    Dim sht As Worksheet
    Dim rng As Range, rngPrint As Range
    Dim startCell As Range
    Dim lastRow As Long
    
    Set sht = Worksheets("Sheet1")                          ' <--- Change if not Sheet1
    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

    Set rngPrint = startCell.Resize(lastRow - startCell.Row + 1, rng.Columns.Count)

    Copy_Sold_By
    
    With sht
        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("AZ56:BC61").Address
            .Zoom = 100
        End With
        Application.PrintCommunication = True
    End With
    
    'rngPrint.PrintPreview
    rngPrint.PrintOut
 
End Sub
```


----------



## Jeddo (Apr 19, 2022)

Alex Blakenburg said:


> Try this:
> Note: I am using Find for the last row on the basis that you don't know which one of the columns is going to have the most rows.
> 
> 
> ...


Thank you sir, I'll try that.  It will be late evening or tomorrow before I get an opportunity to try it out. Your help is much appreciated.


----------



## Jeddo (Apr 20, 2022)

Jeddo said:


> Thank you sir, I'll try that.  It will be late evening or tomorrow before I get an opportunity to try it out. Your help is much appreciated.


I installed the coding and it will print out, but it is printing out as a set range.  If say, I have only 18 rows of data, it still prints out two sheets, the second one being blank. Not acting as a variable print range that sets the print range to the last cell with data. 

Could it possibly be picking up the formulas in those cells as to why it's not printing to the last cell with data? I have formulas in each cell to gather the data from my search range.  I would think it doubtful as the previous coding would go to the last cell, just not include the adjoining columns.  Grasping for straws here.

The second column of all my ranges I am trying to set up will be the one column that will always not have any blank cells. Is there some way to set it to find the last cell with data in column 2 and then move over 1, 2, 3, columns to the right of that to set the range?


----------



## Jeddo (Apr 17, 2022)

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.xlsxABCD1Sold By:    Me23NameBusiness NamePhone #41John 555-123452James 63Paul P&S Cattle Co.555-123674Henry555-123785George96SusanAnytown Auto Parts555-1239107Ginny555-1240118EdithEdith's Closet555-1241129Beatrice555-12421310Eric1411Joe555-12441512ShawnShawn's Landscaping555-12451613Steven1714BobbySheet1


----------



## Jeddo (Dec 18, 2022)

Alex Blakenburg said:


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


Got it to work!
This was for a program for a fund raiser for my church group and at the time, my mind was too cluttered with the upcoming event and I basically ran out of time to get it to work. Had some time, so sat down with a fresh mind and was able to make it work. Thanks so much for your help.


----------



## Alex Blakenburg (Dec 18, 2022)

Thanks for coming back and closing the loop on this. Glad you got it to work.


----------

