Control Shift Down to the next occupied cell

[PCRIDE]

I am recording a macro, I do a Control Shift down arrow to find the next occupied cell then press up arrow and copy\paste. However the macro is adding in a range of cells.

Since the report can change daily and have a different number rows each time it is run, the range will not work.

Please advise

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A10").Select
ActiveCell.FormulaR1C1 = "=RC[1]"
Range("A10").Select
Selection.Copy
Range("B10").Select
Selection.End(xlDown).Select
Range("A827").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste


I need to replace all of these sections Range("A827").Select with a command that finds the next occupied cell.

 

[Diablo II]

Range("A10:A827").Select
Selection.Copy
sheets("the sheet name to past to").activate
Range(" the range were you wont to past").select
Selection.Copy

 

[PCRIDE]

Thanks for the quick reply.

I cannot use a range with specific row numbers, since the amount of rows change each time the report is run.

Anything with Range("A10:A827"). will not work for me.

If I start at the top of the sheet and control shift down to the next occupied cell, it may be 5 rows untill it hits the next occupied cell, on the next run it could be 10 rows until the next occupied cell. This is why the ranges will not work.

 

[Diablo II]

try this it will tell you the first and last rows and then select it it can be any number of rows or columns

Sub test()
Dim rNum As Long
 rNum = Cells.Find(What:="*", After:=Range("a1"), _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious).Row
 MsgBox "last row with an entry: " & rNum
 Dim cNum As Integer
 cNum = Cells.Find(What:="*", After:=Range("a1"), _
   SearchOrder:=xlByColumns, _
   SearchDirection:=xlPrevious).Column
   MsgBox "Last column with an entry:  " & cNum
Range(Range("A1"), Cells(rNum, cNum)).Select
End Sub