Excel macro that hides a given number of rows

Question

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

score 3 · Answer 1 · answered May 26 '17 at 08:40

3

Change

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

to

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

answered May 26 '17 at 08:40

YowE3K

23,852
7
26
40

Nice, I was gone for 2 weeks and you climbed to 12K :) – Shai Rado May 26 '17 at 08:44
2

@ShaiRado - there's more rep available when you aren't answering all the questions :D – YowE3K May 26 '17 at 08:46
@YowE3K - Another veteran returned, shall I call everyone to abandon ship? :)) – Romcel Geluz May 26 '17 at 08:57

Tom · Answer 2 · 2017-05-26T08:57:54.177

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

Excel macro that hides a given number of rows

2 Answers2