Set rng = sh.Range("A5").SpecialCells(xlCellTypeLastCell)

Is this what you mean?
x=inputbox("enter last row")
or
x=cells(65536,"c")
setrng=sh.range("b5:c"&x)

"Paul" wrote in message
om...
I found the macro below on this site, and it is just what I've been
looking for. But... I need one modification, and would appreciate if
you could help me.

The macro below copy information starting in cell A5. I need the macro
to copy all information in the range B5:C??? only, and not from all
other columns in each sheet. The sign ??? is the last row used in each
sheet.


Sub CombineSheets()
Dim counter As Long
Dim i As Integer
Dim copyrange As Range
Dim actsheet As Worksheet
Dim sh As Worksheet
Set actsheet = Worksheets("Master")
counter = 2
actsheet.Cells.ClearContents

For i = 2 To ThisWorkbook.Sheets.Count - 1
Set sh = Worksheets(i)
Set rng = Nothing
On Error Resume Next
Set rng = sh.Range("A5").SpecialCells(xlCellTypeLastCell)
On Error GoTo 0
If Not rng Is Nothing Then
Set copyrange = Range(sh.Range("A5"), rng)
If Rows.Count - counter + 1 copyrange.Rows.Count Then
copyrange.Copy _
actsheet.Cells(counter, 1)
counter = counter + copyrange.Rows.Count
Else
MsgBox "Note enough room"
End If
End If
Next i
End Sub