Selecting a range of rows from row 1 to row Variable 1

[Numbers1]

In an Excel Macro I can not figure out how to write the code to identify the range of rows 1 through a preloaded variable row number. Here is what I have so far:

Sub Test_data_format()

Dim intRowRef As Integer ' row reference of a cell
Dim intColRef As Integer ' column reference of a cell

' Find a particular cell on spreadsheet
Cells.Find(What:="date", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
SearchFormat:=False).Activate
' Record cell address
intRowRef = ActiveWindow.RangeSelection.Row
intColRef = ActiveWindow.RangeSelection.Column

****This line doesn't work!!!
Rows.Range(1, intRowRef).Select

Selection.Delete Shift:=xlUp
End Sub

Can someone help?
Thanks, Numbers

Thanks,
Numbers

 

[Gavona]

You may be better off using Cells not Range. Range expects text values

You are also misusing the Rows property. Try:
Cells(1, intRowRef).rows.select

However you don't need to select:
Cells(1, intRowRef).rows.delete Shift:=xlShiftUp


Gavin

 

[Numbers1]

This doesn't work. I get:
Runtime Error 1004
Application-defined or object-defined error.

I always have a problem with the proper syntax.

Thanks,
Numbers

 

[electricpete]

targetrow=5
mystr="$1:$" + cstr(targetrow)

Now you can refer to range(mystr)

 

[Gavona]

Which line of code gives that error? I don't get it.
I did mix up my rows and columns..and I can't explain why the rows bit has no effect but this does work

Range("a1", Cells(intRowRef, 1)).EntireRow.Delete Shift:=xlShiftUp

I always have a problem with the proper syntax.

My advice would be:
Use VBA help - lots of examples
Browse posts in this forum
Step through code with windows arranged so you can see both the workbook and the VBA editor (and watch window)
Use the watch window

Gavin

 

[Numbers1]

Gavin:
Thank you. That did the trick. I had browsed the site and always step through the code while watching the worksheet. The problem I have is I only do this once or twice a year and often forget.
I appreciate everyone's help and patience on this forum. It's a great feeling when a project comes together.
Thanks, Numbers

Thanks,
Numbers

 

[Gavona]

No problem - I have similar issues sometimes.

If you go back to help on Range you will see that the arguments are always text (in quotes or a string variable).
Range(1, intRowRef) does not do this so you got an error.
Electric Pete showed an alternate solution to mine:
Construct a string:
mystr="$1:$" + cstr(targetrow)
Then plug it into the range object:
Range(mystr)



Gavin