Routine for the cursor and scroll position and also hide the blank rows.

[beloshi]

Hi Excel Gurus,

Once again I am very much thankful to this community due to which I learned a lot. I am stuck in the current routine although it is not giving any error and executing but its not hiding the rows as mentioned which are blank has no data. To be more precise I have selected the sheets for which I want to apply the hidden routine to.

Kindly help

Code I am using is below for reference ;

Sub Position()
Dim iLastRow As Integer
Dim ws As Worksheet


On Error Resume Next
For Each ws In Worksheets
    Application.Goto Reference:=ws.Range("A1"), Scroll:=True
    
    'Hide blank rows
    If ws.Name = "DQ % Stores " Or ws.Name = "DQ # Stores" Or ws.Name = "DQ % New Reg Stores" _
    Or ws.Name = "DQ # New Reg Stores" Or ws.Name = "DQ % Stores per Branch" _
    Or ws.Name = "DQ # Stores per Branch" Or ws.Name = "DQ % Stores Branch New Reg" _
    Or ws.Name = "DQ # Stores Branch New Reg" Then
    
        iLastRow = ws.Cells(129, 2).End(xlUp).Row
        For i = iLastRow + 1 To 129
            ws.Rows(i).Hidden
        Next
    End If
Next
On Error GoTo 0
End Sub

best regards

Beloshi

 

[Fluff]

remove this line & see what happens

On Error Resume Next

 

[My Aswer Is This]

I hardly ever use:
OnError Resume next

What your telling Excel is to ignore all the errors in my code and just keep going.
This can be used when you know a error will occur but you know why the error occurred and still want to ignore it because you know why it occurred.

The reason your not getting a error code is because you told Excel you do not want to be told when a Error occurs.

 

[My Aswer Is This]

Maybe you would like to tell us what your wanting to do.

 

[beloshi]

remove this line & see what happens

On Error Resume Next

ws.Rows(i).Hidden[/COLOR]

Run-time error '1004'

Hidden Method of range class failed

 

[Fluff]

If you want to hide those rows use

ws.Rows(i).Hidden=True

Otherwise change True to False

 

[beloshi]

If you want to hide those rows use

ws.Rows(i).Hidden=True

Otherwise change True to False

It worked like a charm. How can I forget this. Thanks alot Fluff.

Here is the complete code if someone in future get stuck in something like this.

Sub Position()
Dim iLastRow As Integer
Dim ws As Worksheet


'On Error Resume Next
For Each ws In Worksheets
    Application.Goto Reference:=ws.Range("A1"), Scroll:=True
    
    'Hide blank rows
    If ws.Name = "A" Or ws.Name = "B" Or ws.Name = "C" _
    Or ws.Name = "D" Or ws.Name = "E" _
    Or ws.Name = "F" Or ws.Name = "G" _
    Or ws.Name = "H" Then
    
        iLastRow = ws.Cells(129, 2).End(xlUp).Row
        For i = iLastRow + 1 To 129
            ws.Rows(i).Hidden = True
        Next
    End If
Next
On Error GoTo 0
End Sub

 

[Fluff]

Glad we could help & thanks for the feedback