Inserting New Row for each not empty cell - EXCEL VBA

ozonas

New Member
Joined
Aug 6, 2013
Messages
9
Hello Guys,

I have a small problem with my code - it just keeps inserting new rows without executing the if statement. So basically what I want to do is: I have a spreadsheet with lots of data in it and almost an empty column in between with a couple dates there. If there is a date in that column, I want Excel VBA to insert a new row just bellow that date. Here is my code so far:

Sub newmacro()
Dim rng As Range
Dim cell As Range
Set rng = Range("J3:J40")
For Each cell In rng
If Not IsEmpty(cell.Value) Then
ActiveCell.Offset(1, -9).Select
ActiveCell.EntireRow.Insert
ActiveCell.Offset(1, 9).Select
End If
Next
End Sub

Thank you for your help! Explaining where I went wront would be helpful as well!
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,110
Office Version
  1. 365
Platform
  1. Windows
Ah on re read its the activecell bit. Excel will read down from J3 and insert a line if it finds a value but not necessarily after J3. Depends what the activecell is when the macro is run.
 

ozonas

New Member
Joined
Aug 6, 2013
Messages
9
You have missed your 'Else' from your if.

So you mean that instead of writing End if, I should write somethig like: else (do nothing)...next (continue looping) and then end if at the bottom? Could you please explain that in more detail.

Thank you
 

vds1

Well-known Member
Joined
Oct 5, 2011
Messages
1,193

ADVERTISEMENT

Try,

Code:
[COLOR=#0000ff]Sub [/COLOR]newmacro()
[COLOR=#0000ff]Dim[/COLOR] Rng [COLOR=#0000ff]As[/COLOR] Range
[COLOR=#0000ff]For Each[/COLOR] Rng [COLOR=#0000ff]In[/COLOR] Range("J3:J40")
    [COLOR=#0000ff]If[/COLOR] IsDate(Rng.Value) [COLOR=#0000ff]Then[/COLOR]
        Rng.Offset(1, 0).EntireRow.Insert
[COLOR=#0000cd]    End If[/COLOR]
[COLOR=#0000cd]Next[/COLOR]
[COLOR=#0000cd]End Sub[/COLOR]
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,110
Office Version
  1. 365
Platform
  1. Windows
Try adding this bit:

Code:
If Not IsEmpty(cell.Value) Then
    ActiveCell.Offset(1, 0).Select
    ActiveCell.EntireRow.Insert
    ActiveCell.Offset(1, 0).Select
Else
    ActiveCell.Offset(1, 0).Select
End If

Bear in mind that your range may need extending as you are adding rows.
 

ozonas

New Member
Joined
Aug 6, 2013
Messages
9

ADVERTISEMENT

Thank you all. I'm also using quite a lot of offsets as well to make changes to the specific cells after the extra row being inserted (the laborious task being done).

My aim now is to colour cells where the change has happened for instance (please see the red bold line):


Sub newmacro()
Dim Rng As Range
For Each Rng In Range("J3:J40")
If IsDate(Rng.Value)
Then Rng.Offset(1, 0).EntireRow.Insert
Rng.Offset(1,0).Copy Destination: Rng.Offset(1,2).Interior.Color = 5 ~~ not working. Supposed to colour the cell
End If
Next
End Sub


I assume that with this type of code, it might be something among "event triggering" lines: running a private sub etc?? Or is there a shortcut?

Thanks once again!
 

vds1

Well-known Member
Joined
Oct 5, 2011
Messages
1,193
Try,

Code:
Sub newmacro()
Dim Rng As Range
For Each Rng In Range("J3:J40")
If IsDate(Rng.Value) Then
    Rng.Offset(1, 0).EntireRow.Insert
    Rng.Offset(1, 2).Interior.Color = 5
End If
Next
End Sub

what were you trying to copy ?
 

Watch MrExcel Video

Forum statistics

Threads
1,108,969
Messages
5,525,961
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top