Hide Rows using a Macro

[HangMan]

I have several hundred spread sheets coming to me for analysis, the layout is identical on each one. I need to hide particular rows on every single sheet, e.g. Rows 14, 16, 17, 18, and 46 to 51 inclusive. Is there anyway to do this using a macro so I don't have to laboriously do this every time I open each sheet?

Many thanks

[hamjam]

Hello
Is that the same rows on each spreadsheet or do you need to choose which rows based on some criteria ?

If same row you could specific that and run a little macro to clean that out :

Sub test()
    hide_row = Array(14, 16, 17, 18, 46, 47, 48, 49, 50, 51)
    Range("A1").Select
    
    For n = 0 To 9
        ActiveCell.Offset(hide_row(n), 0).EntireRow.Delete
    Next n
    
End Sub

Regards

[hamjam]

Hello
That deletes the row, you can change that to hide the row, probably need another line of code for that.

Regards

[HangMan]

Hi hamjam,

Yes, it would be the same rows on each spreadsheet, but it would definitely need to hide the rows and now delete them, is that possible to do?

Many thanks

[hamjam]

One more tip, I would create an empty spreadsheet with the macro and save the activation of the macro under the event of opening a file. Then I would open the spreadsheets from that template.
In this case, the macro would run automatically and the rows would be removed when it displays in front of you.

Just throwing it out there.

Regards

[hamjam]

Hello
Most things are possible ....

Just change the line in the middle to read :

        ActiveCell.Offset(hide_row(n) - 1, 0).EntireRow.Hidden = True

I also added a '-1' as otherwise you would miss the row by 1 line every time.

Regards

[HangMan]

That works perfectly, many thanks...

You'll have to excuse my ignorance, I think your tip is perfect and will certainly save a lot of time, but how would I actually do that, I'm still very new to VBA!

Regards

[hamjam]

Hello
Perhaps you could mark above as solved. And stars are always welcome.

For the tip, it is a little tricker. In the VBA editor (alt F11) instead of the module where your code is inserted, go up an you will find "thisworkbook". It you double-click it, it should open a blank screen on youur right side with the top left (also called object) saying workbook
If you look to the right of it, another text box show (called procedure)

You can change the procedure to open and then a bit of code appear below. This is the code that will be run when the workbook is opened. You will have to add some code in between the sub and end sub, on the lines of :

    fileToOpen = Application.GetOpenFilename()
    Workbooks.Open Filename:=fileToOpen

And also add the name of the macro, my name was test - as the 3rd line

Play around a bit with that, it may save you some time later.

Regards

[HangMan]

Thanks hamjam, I'll give that a go, I think it is a perfect idea...

Many thanks for your help...