VBA inserting 1 row above each previously filtered row

[Jvalades]

Hello hello again,

As stated above, I have a list of data that i filtered by color. After the filter has been done I want to sort through the leftover list of rows and add a row above each.

As of right now this is what I have to find the rows, the next step being to add a row above each row.

' Store the row count as a variable
Dim TheLastRow As Long
TheLastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row




'Apply filter for InStock Inv
 Rows("7:7").Select
 Selection.AutoFilter
 ActiveSheet.Range("$7:$31").AutoFilter Field:=2, Criteria1:=RGB(204, 255, _
        255), Operator:=xlFilterCellColor

Please advise and thank you.

 

[Jvalades]

Bump

 

[John_w]

Your details are a little sparse, but from your code you seem to be filtering rows 7-31, row 7 being the headings row. See if this does what you want:

Public Sub AutoFilter_and_Insert_Rows2()

    Dim lastRow As Long
    Dim filterRange As Range
    Dim numVisibleRows As Long, visibleRowNumbers() As Long
    Dim i As Long
    Dim cell As Range
    
    With ActiveSheet
        If .AutoFilterMode Then .AutoFilterMode = False
        
        'Autofilter from row 7 (headings row) to row 31
        
        .Rows("7:31").AutoFilter Field:=2, Criteria1:=RGB(204, 255, 255), Operator:=xlFilterCellColor
    
        'Create array to hold row numbers of visible rows, excluding headings row
            
        numVisibleRows = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1
        ReDim visibleRowNumbers(numVisibleRows - 1)
        
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).row
        Set filterRange = .Range("A8:A" & lastRow)
    
        i = 0
        For Each cell In filterRange.SpecialCells(xlCellTypeVisible)
            visibleRowNumbers(i) = cell.row
            i = i + 1
        Next
        
        'Loop backwards through visible row numbers and insert a row above each visible row, using format of visible row
        
        For i = UBound(visibleRowNumbers) To 0 Step -1
            .Rows(visibleRowNumbers(i)).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow 'xlFormatFromLeftOrAbove
        Next
    End With
   
End Sub

 

[Jvalades]

Ahh this is perfect. Thank you

 

[Jvalades]

Your details are a little sparse, but from your code you seem to be filtering rows 7-31, row 7 being the headings row. See if this does what you want:

Public Sub AutoFilter_and_Insert_Rows2()
    
        'Autofilter from row 7 (headings row) to row 31
        
        .Rows("7:31").AutoFilter Field:=2, Criteria1:=RGB(204, 255, 255), Operator:=xlFilterCellColor
    
   
End Sub

John_W
Can we make this filter a stored variable. For instance if I wanted to search more rows than just (7:31)

Maybe like:

Rows(TheLastRow).AutoFilter Field:=2, Criteria1:=RGB(204, 255, 255), Operator:=xlFilterCellColor

??

Is this possible

 

[John_w]

John_W
Can we make this filter a stored variable. For instance if I wanted to search more rows than just (7:31)

Replace the AutoFilter with:

        'Autofilter from row 7 (column headings row) to last row with data in column A
        
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).row
        Set filterRange = .Rows("7:" & lastRow)
        filterRange.AutoFilter Field:=2, Criteria1:=RGB(204, 255, 255), Operator:=xlFilterCellColor

 

[Jvalades]

Replace the AutoFilter with:

        'Autofilter from row 7 (column headings row) to last row with data in column A
        
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).row
        Set filterRange = .Rows("7:" & lastRow)
        filterRange.AutoFilter Field:=2, Criteria1:=RGB(204, 255, 255), Operator:=xlFilterCellColor

You are the best!

Thank you again for all of then help