CODEWITHSUNDEEP
vbaexcel
Miguel
Miguel

Reputation: 11

Excel macro that hides a given number of rows

I was searching for answers regarding this issue, but no luck. I want an excel macro to hides rows based on a cell value. My table is between row 18 and row 418 could you please take a look at my VBA code:

Sub HideRows()

Dim np As Integer

np = Range("W1").Value
'hidding all the table rows first                
ActiveSheet.Rows("18:418").EntireRow.Hidden = True
'showing only from row 18 to the number given by cell "W1"
ActiveSheet.Rows("18:np").EntireRow.Hidden = False

End Sub

It is giving me a type mismatch an error, is there any way to make this work? Thank you so much

Upvotes: 1

Views: 43

Answers (2)

Tom
Tom

Reputation: 9898

Give this a go

Sub HideRows()

Dim np As Long

    np = Range("W1").Row
    'hidding all the table rows first                
    ActiveSheet.Range("18:418").EntireRow.Hidden = True
    'showing only from row 18 to the number given by cell "W1"
    ActiveSheet.Range("18:np").EntireRow.Hidden = False

End Sub

Also, it's better to Dim most numbers as Long rather than Integer. Long has a longer character size (i.e. you can use bigger numbers where as Integer's max is ±32767) This is worth noting if you're looping through a large data set. Also VBA treats them exactly the same on a 32-Bit environment - When it compiles it converts Integer's toLong`. Int & Long Reference

Upvotes: 0

YowE3K
YowE3K

Reputation: 23994

Change

ActiveSheet.Rows("18:np").EntireRow.Hidden = False

to

ActiveSheet.Rows("18:" & np).EntireRow.Hidden = False

Upvotes: 3

Related Questions