Adding a blank line above a row with a non-blank cell

laniw75

New Member
Joined
Dec 18, 2010
Messages
15
I need some more vba expertise.

I need a blank row inserted above any non-blank cell in column A.

Can anyone please advise?

Thanks
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

rudfaden

New Member
Joined
Feb 5, 2011
Messages
48
This macro should do it. But note that if you do entire column A it will take some time

Code:
Sub InsertRow()
Dim i As Long

For i = 1 To 5 'insert all the row you want.
    If Len(Cells(i, 1).Value) = 0 Then
        Cells(i, 1).EntireRow.Insert shift:=xlUp 'Inserts row above
        i = i + 1 'skip to next row
    End If
Next i

End Sub
 

rudfaden

New Member
Joined
Feb 5, 2011
Messages
48
You may wanna add

Code:
application.screenupdating=false
application.screenupdating=true
aroudn the code to speed things up
 

laniw75

New Member
Joined
Dec 18, 2010
Messages
15
Hi again,

It appears to be adding a line above the blank cells, I need it to add a line above the non-blank cell.

Thanks
 

rudfaden

New Member
Joined
Feb 5, 2011
Messages
48

ADVERTISEMENT

Try this:

Code:
application.screenupdating=false

Sub InsertRow()
Dim i As Long

For i = 1 To 5 'insert all the row you want.
    If Len(Cells(i, 1).Value) = 0 Then
        Cells(i, 1).EntireRow.Insert shift:=xlDown 'Inserts row above
        i = i + 1 'skip to next row
    End If
Next i

application.screenupdating=true
End Sub
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,918
Office Version
365
Platform
Windows
If you are looping through the rows to add, it's best to work from bottom to top. Try ..

<font face=Courier New><br><br><br><br><SPAN style="color:#00007F">Sub</SPAN> Insert_Rows()<br>    <SPAN style="color:#00007F">Dim</SPAN> LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    LR = Range("A" & Rows.Count).End(xlUp).Row<br>    <SPAN style="color:#00007F">For</SPAN> r = LR <SPAN style="color:#00007F">To</SPAN> 1 <SPAN style="color:#00007F">Step</SPAN> -1<br>        <SPAN style="color:#00007F">If</SPAN> Len(Range("A" & r).Value) > 0 <SPAN style="color:#00007F">Then</SPAN><br>            Rows(r).Insert<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> r<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 
Last edited:

laniw75

New Member
Joined
Dec 18, 2010
Messages
15

ADVERTISEMENT

Your VBA worked like a dream, thanks Peter!

Any chance we can enter code to turn the row yellow?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,918
Office Version
365
Platform
Windows
Any chance we can enter code to turn the row yellow?
Sure, but do you mean the newly inserted blank one or the one with the non-blank value in column A?
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,101
Messages
5,509,244
Members
408,717
Latest member
Bea2136

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top