Hiding rows in a dynamic range

acollins

Board Regular
Joined
Aug 1, 2007
Messages
63
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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

brian.wethington

Well-known Member
Joined
Jul 20, 2006
Messages
1,739
Welcome to the board,

Give this a shot.

Code:
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

Board Regular
Joined
Aug 1, 2007
Messages
63
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. :biggrin:
 

scoha

Active Member
Joined
Jun 15, 2005
Messages
428
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

Well-known Member
Joined
Jul 20, 2006
Messages
1,739
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:

Code:
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
 

Forum statistics

Threads
1,181,658
Messages
5,931,268
Members
436,785
Latest member
KingGideon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top