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.


22229

[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