CODEWITHSUNDEEP
excelvba
user2532928
user2532928

Reputation: 69

Insert row if there is text in column A

I found this VBA Code:

Sub InsertRev()
Dim c As Range
Set Rng = ActiveSheet.Range("A1:A5000")
For dblCounter = Rng.Cells.Count To 1 Step -1
    Set c = Rng(dblCounter)
    If c.Value Like "*Card Number:*" Then
    c.EntireRow.Insert
End If
Next dblCounter
End Sub

but it only works if I have specific text in A. Is there way to change if so there's a row inserted above any cell in column A that has text? Can the row also have the same value that was in column A above it (So if A1 has text, row is inserted with the value of A1 (which is now A2)).

Upvotes: 1

Views: 64

Answers (1)

Vityata
Vityata

Reputation: 43595

"Is there way to change if so there's a row inserted above any cell in column A that has text?"

Instead of:

If c.Value Like "*Card Number:*" Then

Write:

If Len(Trim(c)) Then

"Can the row also have the same value that was in column A above it (So if A1 has text, row is inserted with the value of A1 (which is now A2))."

After c.EntireRow.Insert write:

If c.Row > 1 Then c.Value = c.Offset(-1).Value

The c.Row > 1 check is needed, because if it is the first cell, the c.Offset(-1) would trigger an error. MSDN Range.Offset

Upvotes: 1

Related Questions