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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,717
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,200

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,717
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,200
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 ?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,826
Messages
5,855,875
Members
431,771
Latest member
CoryMelth

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
Top