How to insert a blank row based on cell value

Question

I am trying to look for cell values in column C and insert a blank row just above the cell if it doesn't contain a dot (".").

I tried the following macro:

Sub testing()
    Dim col As Variant
    Dim lr As Long
    Dim i As Long
    Dim startRow As Long

    col = "C"
    startRow = 2
    lr = Cells(Rows.Count, col).End(xlUp).Row

    With ActiveSheet
        For i = lr To startRow Step -1
            If IsNumeric(Range("E2", "E" & lr).Value) = True Then
                .Cells(i + 1, col).EntireRow.Insert shift:=xlUp
            End If
        Next i
    End With
End Sub

Input

Desired Output

Tim Williams · Accepted Answer · 2022-03-22 16:47:37Z

5

A slightly different approach where the insert happens at the end:

Sub Tester()

    Dim c As Range, rng As Range, ws As Worksheet, v
    
    Set ws = ActiveSheet
    
    For Each c In ws.Range("C2:C" & ws.Cells(Rows.Count, "C").End(xlUp).Row).Cells
        v = c.Value
        If InStr(v, ".") = 0 And Len(v) > 0 Then    'has a value, with no "." ?
            If rng Is Nothing Then                  'any previous cells found?
                Set rng = c                         'start the range with `c`
            Else
                Set rng = Application.Union(c, rng) 'add `c` to `rng`
            End If
        End If
    Next c
    'if found any cells then do the insert
    If Not rng Is Nothing Then rng.EntireRow.Insert shift:=xlDown
End Sub

edited Mar 22, 2022 at 16:47

answered Mar 22, 2022 at 16:31

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rahul Vaidya Over a year ago

i tried this but not sure why it's not working, i will recheck again and update

Rahul Vaidya Over a year ago

it's working as intended

Iziminza · Accepted Answer · 2022-03-22 16:30:21Z

3

There are two mistakes in your code:

You are checking IsNumeric on the whole column E up to the row you are currently working on. But multiple cells don't have a numeric value.
You are inserting rows below the cells that contain a dot. You want to insert rows above the cells that don't contain any dot.

It should be

Sub testing()
    Dim col As String
    Dim i As Long
    Dim startRow As Long
    Dim lastRow As Long
    
    col = "C"
    startRow = 2
    lastRow = Cells(Rows.Count, col).End(xlUp).Row
    
    With ActiveSheet
        For i = lastRow To startRow Step -1
            If IsNumeric(Range("E" & i).Value) = False Then
                .Cells(i, col).EntireRow.Insert shift:=xlDown
            End If
        Next i
    End With
End Sub

You could even do away with the column E, since VBA contains a function called InStr that looks for characters or strings inside another string, like this:

If InStr(Range("C" & i).Value, ".") = 0 Then
    ' ...
End If

answered Mar 22, 2022 at 16:30

Iziminza

3993 silver badges10 bronze badges

3 Comments

Rahul Vaidya Over a year ago

Thanks, it is working but inserting a row below the cell value which doesnot have a "." , is it possible to insert it above the cell value which doesnt have a dot as described in output image

Rahul Vaidya Over a year ago

i have tried shift:=xlup as well but no luck

Iziminza Over a year ago

It's the i+1 in your code that makes it insert the row beneath the cell. Remove the +1.

Collectives™ on Stack Overflow

How to insert a blank row based on cell value

2 Answers 2

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related