How to run a loop through filtered list in excel

user_19491248 0
Viewed 63

i am trying to run this loop/code through a filtered list in excel where the row numbers are not in sequence eg the first row that meets the filtered criteria could be row 3, followed by row 7 then row 34 for instance.

this is my current code which runs for every row in the stated range but it will not work through for filtered list. How would you modify the code such that this loop can run through a filtered list?

Sub specialloop()
Dim i As Integer
Dim j As Integer
Dim input_var As String
lastrow = Cells(Rows.Count, 1).End(xlUp).row
rowinput = InputBox("input row number to start from")
j = rowinput
For i = j To lastrow

Cells(i, 26).Select
input_var = InputBox("degree verify")
ActiveCell.Value = UCase(input_var)
ActiveCell.Offset(0, 8).Select

input_var = InputBox("med invoice date")
ActiveCell.Value = UCase(input_var)
ActiveCell.Offset(0, 1).Select

input_var = InputBox("med clear")
ActiveCell.Value = UCase(input_var)
ActiveCell.Offset(1, -9).Select
 

Next i
End Sub
vba7 vba excel
2 Answers

The quick and dirty way would just be to add a condition in your loop for the RowHeight. If that is 0 then the row is filtered.

For i = j To lastRow

    If ActiveSheet.Rows(i).RowHeight > 0 Then
        Cells(i, 26).Select
        input_var = InputBox("degree verify")
        ActiveCell.Value = UCase(input_var)
        ActiveCell.Offset(0, 8).Select

        input_var = InputBox("med invoice date")
        ActiveCell.Value = UCase(input_var)
        ActiveCell.Offset(0, 1).Select

        input_var = InputBox("med clear")
        ActiveCell.Value = UCase(input_var)
        ActiveCell.Offset(1, -9).Select
    End If

Next i

You might want to look at how-to-avoid-using-select-in-excel-vba

user_66009400

How to iterate over visible data

For this purpose you can use Range.SpecialCells method:

Sub SpecialLoop()
' Loop over visible cells in Data range
' and fill in some data (see Shift... constants below)
Dim Data as Range
Dim Cell as Range
Dim FirstRow as Long, LastRow as Long
Dim input_var As String
Const ShiftDegree = 25
Const ShiftInvoice = ShiftDegree + 8
Const ShiftMedClear = ShiftInvoice + 1
    With ActiveSheet    
        FirstRow = InputBox("input row number to start from")
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        Set Data = .Range(.Cells(FirstRow, 1), .Cells(LastRow, 1))
    End With
    For Each Cell in Data.SpecialCells(xlCellTypeVisible)
        input_var = InputBox("degree verify")
        Cell.Offset(0, ShiftDegree).Value = UCase(input_var)
        input_var = InputBox("med invoice date")
        Cell.Offset(0, ShiftInvoice).Value = UCase(input_var)
        input_var = InputBox("med clear")
        Cell.Offset(0, ShiftMedClear).Value = UCase(input_var)
    Next Cell
End Sub

Notes

  1. Row numbers should be Long, not Integer
  2. Here I assume that meaningful filtered data are collected in the first column
  3. Try if not Cells(i, 26).EntireRow.Hidden then ... as an alternative
user_149096210
Related