Insert Row Logic in VBA Macro

[chapcrap]

So, I've been working with different ideas for a couple of days and can't get it. I've taken too much time off from writing macros and don't know what I'm doing anymore! This should be easy...

So, almost daily, I am going to have a different data set. I want to filter on a specific item (255000) in that data set in a specific column (E). Then I want to insert a new row above each of the filtered rows. There is some other formatting I'm going to add as well, but this is part that's tripping me up. Just adding the rows in the correct places.

 

[The_Macrotect]

Hi Chapcrap,

Is this the result you're looking for after the data is filtered?

All the best,
Matt

 

[JLGWhiz]

This is probably the easiest way to get the roww with the criteria and then insert the blank rows.

Sub t()
Dim i As Long, adr As String
With ActiveSheet
    For i = .Cells(Rows.Count, "E").End(xlUp).Row To 2 Step -1
        If .Cells(i, "E") = 255000 Then
            Rows(i).Insert
        End If
    Next
End With
End Sub

It does not use a filter.

 

[JLGWhiz]

You can filter the range of cells on column E and then select only those filtered entire rows with the criteria item. Then right click and select 'Insert Row'.

 

[chapcrap]

Hi Chapcrap,

Is this the result you're looking for after the data is filtered?

View attachment 15564

All the best,
Matt

Yes. That's the desired result.

 

[chapcrap]

You can filter the range of cells on column E and then select only those filtered entire rows with the criteria item. Then right click and select 'Insert Row'.

I forgot this was a thing... this helps as well.

 

[chapcrap]

This is probably the easiest way to get the roww with the criteria and then insert the blank rows.

Sub t()
Dim i As Long, adr As String
With ActiveSheet
    For i = .Cells(Rows.Count, "E").End(xlUp).Row To 2 Step -1
        If .Cells(i, "E") = 255000 Then
            Rows(i).Insert
        End If
    Next
End With
End Sub

It does not use a filter.

This works. Also, once you reminded me about the visible cells only, this also works:

Sub InsertRow()
'
'
    AutoFilter Field:=5, Criteria1:="255000"
    Range("E1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.EntireRow.Insert
    Range("E1").Select
    Selection.EntireRow.Delete
End Sub

 

[JLGWhiz]

Thanks for the feedback.
regards, JLG