Hiding rows in a dynamic range

[acollins]

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

 

[brian.wethington]

Welcome to the board,

Give this a shot.

Sub Test

Dim rngCell As Long
rngCell = Range("A1").End(xlDown).Offset(1, 0)
Rows(rngCell & ":2499").EntireRow.Hidden = True

End Sub

~Edit, changed integer to long, this will probably not have effected you but it is generally a good idea to use a long when pertaining to rows.

 

[acollins]

It worked like a charm. Thanks for the quick response, Brian!

I'm really just getting my feet wet with Excel macros. I have a feeling I will be posting here often.

 

[scoha]

Hiding rows when blank

Can you tell me how to modify this to a situation where it finds the first row that has a zero value?? I have a similar requirement to above except my worksheet has 80 lines of index driven results - so every line in the 80 has a formula in it but these evaluate to a different number of lines with actual results in? I want to automatically hide those rows that havent evaluated to a result.

Hope you can help.

 

[brian.wethington]

Give this a shot... you will have to change the sheet name and the range of Rng to fit your data and the range you want to look at:

Sub Test()
Dim Rng As Range, C As Range, FirstAddress As String

Sheets("Sheet1").Cells.EntireRow.Hidden = False
Set Rng = Sheets("Sheet1").Range("A2:A81")

With Rng
    Set C = .Find(0, LookIn:=xlValues)
    If Not C Is Nothing Then
        FirstAddress = C.Address
        Do
            C.EntireRow.Hidden = True
            Set C = .FindNext(C)
        Loop While Not C Is Nothing And C.Address <> FirstAddress
    End If
End With

End Sub