Macro to hide non-contiguous rows in excel

[kwoifo]

Hi,

I have a macro in a sheet that allows a user to hide some specific non-contiguous rows. It works, but is quite slow unfortunately. I assume there is a better way I can write the code to improve the speed. I am currently using this:

Sub Hide()
ActiveSheet.rows("16:17").Hidden = True
ActiveSheet.rows("19:20").Hidden = True
ActiveSheet.rows("22:23").Hidden = True
ActiveSheet.rows("27:28").Hidden = True
ActiveSheet.rows("30:31").Hidden = True
ActiveSheet.rows("33:33").Hidden = True
ActiveSheet.rows("35:35").Hidden = True
ActiveSheet.rows("38:39").Hidden = True
ActiveSheet.rows("41:41").Hidden = True
End Sub

Any suggestions on how to improve it? Currently takes about 5secs for all the rows to be hidden

Many thanks!!

 

[JoeMo]

Hi,

I have a macro in a sheet that allows a user to hide some specific non-contiguous rows. It works, but is quite slow unfortunately. I assume there is a better way I can write the code to improve the speed. I am currently using this:

Sub Hide()
ActiveSheet.rows("16:17").Hidden = True
ActiveSheet.rows("19:20").Hidden = True
ActiveSheet.rows("22:23").Hidden = True
ActiveSheet.rows("27:28").Hidden = True
ActiveSheet.rows("30:31").Hidden = True
ActiveSheet.rows("33:33").Hidden = True
ActiveSheet.rows("35:35").Hidden = True
ActiveSheet.rows("38:39").Hidden = True
ActiveSheet.rows("41:41").Hidden = True
End Sub

Any suggestions on how to improve it? Currently takes about 5secs for all the rows to be hidden

Many thanks!!

Use the Application.Union method to form a non-contiguous range. Then hide that range.

Dim R as range
Set R = Union(ActiveSheet.rows("16:17"),ActiveSheet.rows("19:20"), ...)
R.hidden = True

 

[kwoifo]

Joe -- thanks a lot for the quick reply!!

I have tried your solution but get runtime error 1004 (application-defined or object-defined error) ? Am I doing something wrong? Many thanks

 

[Rick Rothstein]

Try it like this...

Rows("16:39").Hidden = True
Range("18:18,21:21,24:26,29:29,32:32,34:34,36:37").EntireRow.Hidden = False

 

[kwoifo]

Rick that does the trick! Absolutely fantastic! Many thanks!!!

 

[JoeMo]

Joe -- thanks a lot for the quick reply!!

I have tried your solution but get runtime error 1004 (application-defined or object-defined error) ? Am I doing something wrong? Many thanks

Sorry, that should be R.EntireRow.Hidden = True

 

[Rick Rothstein]

Rick that does the trick! Absolutely fantastic! Many thanks!!!

You are welcome; however, I would rather you do it this way instead. Originally, I thought the two-step method would be more compact, but doing the hiding in one step directly is not all that much longer. So I would do it with this single line of code instead of the two lines of code I gave you earlier...

Range("16:17,19:20,22:23,27:28,30:31,33:33,35:35,38:39,41:41").EntireRow.Hidden = True