Select range till last empty row

[alorah.malor]

For the excel sheet, I'd like to print multiple sheets automatically. The printing VBA code I can mangage, but selecting the range is too hard. I need to select in the tab "RD" from A1 till the first empty row, and for the tab "IB" from the cell C1 till the same row as before, but -4 (due to lay-out). I can't let it select till the last empty row on that tab, because due to other formula's there are automatically values in there.

Anyone who can help me? In the attachment you can also see the range it should select after the macro has run.


DSVS-17-KW01.xlsm

[Paul_Hossler]

I didn't see any ranges in the attachment that were selected

Did you really want all those blank pages printed?

This macro will find the useful data on the two sheets using what I understood to be the rules

If you want something else, give the specific addresses of the ranges you want

Option Explicit
Sub FindRows()
    Dim rUsed As Range, rWanted As Range
    Dim r As Long, c As Long
    
    With Worksheets("RD")
        Set rUsed = .UsedRange
        
        r = rUsed.Rows.Count
        Do While Application.WorksheetFunction.Count(rUsed.Rows(r)) = 0
            r = r - 1
        Loop
    
        c = rUsed.Columns.Count
        Do While Application.WorksheetFunction.Count(rUsed.Columns(c)) = 0
            c = c - 1
        Loop
    
        Set rWanted = Range(.Cells(1, 1), .Cells(r, c))
    
        MsgBox "RD range = " & rWanted.Address
    End With
    
    
    With Worksheets("IB")
    
        Set rUsed = .UsedRange
    
        c = rUsed.Columns.Count
        Do While Application.WorksheetFunction.Count(rUsed.Columns(c)) = 0
            c = c - 1
        Loop
        Set rWanted = Range(.Cells(1, 3), .Cells(r - 4, c))
        MsgBox "IB range = " & rWanted.Address
    End With
End Sub

[alorah.malor]

Thanks Paul,

I needed the useful range to be selected, but that is easy to extract from what you wrote. This way I can let it print out the selected range.

Thanks for your help
Flemming