Inserting blanks

[Dallman Ross]

I'd like to insert a blank row below any row where the
conditional-format bottom border in the range is dark blue (code
5). Help appreciated.

=dman=

 

[JE McGimpsey]

What's the condition in the CF?

Use the same condition to check your cells/rows

 

[Dallman Ross]

What's the condition in the CF?

Use the same condition to check your cells/rows

Ah, but the CF uses Excel formulas -- =$A2<>$A3 -- but
I want to insert new rows now in a macro and don't -- oh,
I see what you're saying. I was thinking I needed to look
for the blue line, but I can just look for $A2<>$A3 in the
macro too. Okay, good. Still, is there a faster way than
iterating through all the rows?

=dman=

 

[Dallman Ross]

In <[email protected]>, Dallman Ross <dman@localhost.>
spake thusly:

Okay, this took me a bit of effort to get to work, because I'm
struggling with my low-intermediate knowledge of VBA (up from
plain "low" a month or so ago). But this (below) works, though it's
a bit slow. Anybody see a way to improve it?

Also, a more specific question: you can see where I have to
remove 3 lines from my iLastRow calculation. That's because the
data ends and then has some extraneous matter below after a
blank line. I'd rather just count from the top row down until the
blank line, but am not sure how to do that.


This group has been tremendous help, btw. I scan things when
I have time and slowly build up a repertoire of insight and
tricks.

This is just a stub of what will be much longer. I'm building
it bit-by-bit.

=====
Sub wsMakeRG()

Dim i, iLastRow, iLastCol, xtraRows As Long

ThisWorkbook.Sheets(1).Activate
ActiveSheet.UsedRange.FormatConditions.Delete
Range("A1").Select
Selection.QueryTable.Refresh BackgroundQuery:=False

xtraRows = 3 '// extraneous rowcount
iLastRow = Cells(Rows.Count, "C").End(xlUp).Row - xtraRows
iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
Debug.Print iLastRow, iLastCol


With Range("A1", Cells(iLastRow, iLastCol)).Select
Call sortRg '// preferrential sort
Call cfDeltaRows '// CF blue bottom border when data shifts
End With

For i = iLastRow - 1 To 2 Step -1 '// insert row when data shifts
Debug.Print i
If Cells(i, "A") <> Cells(i + 1, "A") Then
Rows(i + 1).Insert Shift:=xlDown
End If
Next

End Sub


========================