I am trying to write a macro that will automatically hide a range of rows in a worksheet. The problem is that the start range is dynamic, depending on how many rows of data I'm importing. The end range is constant - it will always be row 2499. Here's the code I'm using:
Dim rngCell As Integer
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
rngCell = ActiveCell.Row
Rows("rngCell:2499").Select
Selection.EntireRow.Hidden = True
End Sub
Basically, what I'm trying to do is have the cursor find the first blank cell after the last row of data. Then I'm putting the row number into the variable rngCell. When I run the macro, I get a "Type mismatch" error on the line Rows("rngCell:2499").Select. When I highlight rngCell, the value is 1926, which is the correct row number for the sheet I am importing. If I substitute the actual number 1926 in the macro, it works.
I have tried using Range and several other number types besides Integer (long, double, etc.) to define the variable type, but I still get the "type mismatch" error. Also, Integer is the only one I've tried so far that actually puts a value into rngCell.
Can somebody tell me what I am doing wrong? Is there a better way to do it? Any input is greatly appreciated.
Thanks,
Alan
Dim rngCell As Integer
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
rngCell = ActiveCell.Row
Rows("rngCell:2499").Select
Selection.EntireRow.Hidden = True
End Sub
Basically, what I'm trying to do is have the cursor find the first blank cell after the last row of data. Then I'm putting the row number into the variable rngCell. When I run the macro, I get a "Type mismatch" error on the line Rows("rngCell:2499").Select. When I highlight rngCell, the value is 1926, which is the correct row number for the sheet I am importing. If I substitute the actual number 1926 in the macro, it works.
I have tried using Range and several other number types besides Integer (long, double, etc.) to define the variable type, but I still get the "type mismatch" error. Also, Integer is the only one I've tried so far that actually puts a value into rngCell.
Can somebody tell me what I am doing wrong? Is there a better way to do it? Any input is greatly appreciated.
Thanks,
Alan