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!
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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.
 
Upvote 0
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
 
Upvote 0
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]
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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 ?
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top