Need to move to the next visible row in VBA

[iswarya]

Hi,
I'm trying to accomplish this in VBA. I have a sheet where many rows are hidden. Through VBA, I need to iterate through the rows that are visible only. Is there a way to do this?



Cross-posted at excelforum.com

 

[Jon von der Heyden]

Hello

What are you doing within the iterations?

Example using SpecialCells(xlCellTypeVisible):

[COLOR="Blue"]Public[/COLOR] [COLOR="Blue"]Sub[/COLOR] demo()
    [COLOR="Blue"]Dim[/COLOR] rngRow [COLOR="Blue"]As[/COLOR] Range
    [COLOR="Blue"]For[/COLOR] [COLOR="Blue"]Each[/COLOR] rngRow [COLOR="Blue"]In[/COLOR] Selection.SpecialCells(12).Rows
        [COLOR="Blue"]Debug.Print[/COLOR] rngRow.Address
    [COLOR="Blue"]Next[/COLOR] rngRow
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[CMIIandExcel]

Hi

Try this

Option Explicit
Option Compare Text

Sub VisibleLines()
  Dim iRow As Long, iRows As Long
  Dim Sh As Worksheet
  Set Sh = ActiveSheet
  iRows = Sh.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  For iRow = 1 To iRows
    If Not Sh.Rows(iRow).Hidden = True Then
       MsgBox iRow & " Not Hidden"
    Else
      MsgBox iRow & " Hidden"
    End If
    
  Next iRow

End Sub

Regards

Mike

 

[CMIIandExcel]

Jon's code would be more efficient

Regards

 

[iswarya]

Thanks that was helpful, but am not sure it solves my problem. What I'm looking for is something like this:

Say I have row43 and the next visible row is row62 and after that row71. Within the VBA code I need to be able to compare something in row43 and row62, and if that condition is true, I need to hide row43. In the next iteration I would compare row62 and row71, and repeat the same thing.

Hope I made myself clear.

 

[Jon von der Heyden]

Hi

Sorry but I'm struggling a little to conjure up an example without the specifics. But this is what I came up with and hopefully you can understand it and adapt it to work for your requirement:

[COLOR="Blue"]Public[/COLOR] [COLOR="Blue"]Sub[/COLOR] demo()
    [COLOR="Blue"]Dim[/COLOR] rngRow [COLOR="Blue"]As[/COLOR] Range, rngPreviousRow [COLOR="Blue"]As[/COLOR] Range
    [COLOR="Blue"]For[/COLOR] [COLOR="Blue"]Each[/COLOR] rngRow [COLOR="Blue"]In[/COLOR] Selection.SpecialCells(12).Rows
        [COLOR="Blue"]If[/COLOR] [COLOR="Blue"]Not[/COLOR] rngPreviousRow [COLOR="Blue"]Is[/COLOR] [COLOR="Blue"]Nothing[/COLOR] [COLOR="Blue"]Then[/COLOR]
            [COLOR="Green"]'! compare the first cell in the row with the first cell in the previous visible row[/COLOR]
            [COLOR="Green"]'! if they match then hide the row[/COLOR]
            rngRow.Hidden = (rngRow.Resize(, 1).Value = rngPreviousRow.Resize(, 1).Value)
        [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
        [COLOR="Blue"]Set[/COLOR] rngPreviousRow = rngRow
    [COLOR="Blue"]Next[/COLOR] rngRow
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[CMIIandExcel]

Just for comparison

heres mine

Sub VisibleLines()
  Dim iRow As Long, iRows As Long
  Dim Sh As Worksheet
  Dim vValue1 As Variant, iValue1Row As Long
  Dim vValue2 As Variant, iValue2Row As Long
  Dim bFirstRow As Boolean
  Set Sh = ActiveSheet
  bFirstRow = True
  iRows = Sh.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  For iRow = 1 To iRows
    If Not Sh.Rows(iRow).Hidden = True Then
     If Not bFirstRow Then
         vValue2 = Sh.Cells(iRow, 1).Value ' Add Value Two in this case column 1
        iValue2Row = iRow ' Add row of second value
        If vValue1 <> vValue2 Then ' Change for your test here
          Sh.Rows.Address(ivalue1row).Hidden = True ' hide the first row
          vValue1 = vValue2 ' Swap Values
          iValue1Row = iValue2Row ' Swap Values
        End If
     Else
        vValue1 = Sh.Cells(iRow, 1).Value ' Add Value one in this case column 1
        iValue1Row = iRow ' Add row of first value
     End If
    Else
      MsgBox iRow & " Hidden"
    End If
    
  Next iRow

End Sub

I havent tested it as the boss is in and i need to start looking busy, Looks can be deceptive

Regards

Mike

Edit

Just tried compiling and it failed on the Row.Address will need fixing

 

[iswarya]

Yes, exactly what I was looking for. Thanks Jon! Will try this out.

 

[Jay5472]

I know this thread is old, but it's the first link I get from Google, so I'll post what works for me.

Call MyMacro()
ActiveCell.Offset(1, 0).Activate
Do Until Selection.EntireRow.Hidden = False
If Seletion.EntireRow.Hidden = True Then
ActiveCell.Offset(1, 0).Activate
End If
Loop

 

[danielphayward]

Super old thread I know but still one of the first things that comes up in google.

I've had problems with loops taking too long so I thought I would try to come up with something that doesn't use loops. This assumes that your data is already filtered and there are less than 500,000 lines of data in your sheet.

Sub VisibleData()Dim ColNum, EndCol, BegCol, BegRow As Integer
Dim EndRow As Long

ColNum = 2
EndRow = Cells(500000, ColNum).End(xlUp).Row

EndCol = 8
BegCol = 1
BegRow = 7

Range(Cells(BegRow, BegCol), Cells(EndRow, EndCol)).SpecialCells(xlCellTypeVisible).Select

End Sub

BegCol/Beg Row are where you want to start your selection from and EndCol/EndRow where they end. This macro also assumes that your dynamic vertically only.