CODEWITHSUNDEEP
vbaexcel
shiven
shiven

Reputation: 421

Hide Rows in excel VBA

I am trying to hide rows using VBA. I have the start row from where I have to start hiding the rows. I have written the following code:

sht.Rows(32 + end_row).Resize(, 65536 - (32 + end_row)).Select
Selection.EntireRow.Hidden = True

where end_row is the row til which the data will be populated in my excel sheet and 32 is the index of the row from which data will start populating. When i am trying to run this code it gives an error,

application-defined or object-defined error

How do I resolve this and what should be the best way to do it?

Upvotes: 1

Views: 4363

Answers (2)

Sam
Sam

Reputation: 7303

In this statement.

Resize(, 65536 - (32 + end_row)).Select

you mix up rows and columns..

you probably mean Resize(65536 - (32 + end_row)).Select

Resize([Rows],[Columns])

Much better to write something like this though (avoid use of Select)

Sheet1.Rows.Resize(65536 - (32 + end_row)).EntireRow.Hidden = True

This code hides all the rows at the top of the sheet though and leaves the rows at the bottom of the sheet visible. I assume that you want this the other way round.

This piece of code may help. It will hide all blank rows below the last entry in Column A

Dim sht As Worksheet
Set sht = Sheet1

sht.Range(sht.Cells(sht.Rows.Count, 1), sht.Cells(sht.Rows.Count, 1).End(xlUp).Offset(1)).Rows.Hidden = True

Upvotes: 2

Guilhem Hoffmann
Guilhem Hoffmann

Reputation: 962

An other way could be : 

'Expected sht is a sheet object, else use : ActiveSheet.Rows("32:" & end_row).Hidden = True
sht.Rows("32:" & end_row).Hidden = True

Avoid to use the 65535 limitation

Upvotes: 0

Related Questions