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

[laniw75]

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

 

[laniw75]

Thanks Peter, it worked perfectly!

I wish I had your brain

 

[cardosan]

@Peter_SSs codes are not readable, at least to me :

<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>

would you please reshare? I need to do exactly the same thing

 

[Fluff]

Hi & welcome to MrExcel.
I have edited the post to removes the old HTML code.

 

[cardosan]

Many thanks @Fluff.

I have tried to add automatically the line when a cell is filled in col A as below:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
Call Insert_Rows
End If
End Sub

Sub Insert_Rows()
    Dim LR As Long, r As Long
   
    Application.ScreenUpdating = False
    LR = Range("A" & Rows.count).End(xlUp).Row
    For r = LR To 1 Step -1
        If Len(Range("A" & r).Value) > 0 Then
            Rows(r).Insert
        End If
    Next r
    Application.ScreenUpdating = True
End Sub

but I get:

Run-time error '-21474178-48 (80010108)': Method 'Insert' of object "Range' failed.

Any on why and how to fix this?

 

[Peter_SSs]

Any on why and how to fix this?

Perhaps give us a small set of dummy sample data with XL2BB and explain in relation to that exactly what you are trying to achieve?

 

[cardosan]

@Peter_SSs I have something like the following.
What I want to achieve is that every time a cell in column C is filled a new row under it is added. E.g. once cell C3 is filled a new row is added under it so that the Category energy becomes of 3 rows instead of 2.
Obiovusly this applies only to those rows colored since e.g. row 7 has to remain blank.
Hope this clarify and thanks ahead!

20220310_data_format.xlsm
	A	B	C	D	E
1		Input type	Material Flow - refer to the sheet Figure	Output/Select	Did you buy this material or produce it on your own?
2	Activity	Category	Text	Flow	Origin
3	jui	Energy
4
5		Transport
6
7
8	gyhu	Energy
9
10		Transport
11
12
13
Test_2 (2)

Cells with Data Validation
Cell	Allow	Criteria
D8:D11	List	=F_hide!A36#
E8:E11	List	=F_hide!$A$4:$A$5
D3:D6	List	=F_hide!A29#
E3:E6	List	=F_hide!$A$4:$A$5

 

[Peter_SSs]

What I want to achieve is that every time a cell in column C is filled

Are you only interested in column C?

What is supposed to happen if C3 gets data and 'energy' becomes 3 rows and then C3 is cleared leaving 'energy' with 3 blank rows?

What happens if C4 is filled in before C3?

this applies only to those rows colored since e.g. row 7 has to remain blank.

Is the code supposed to check/enforce nothing in that row or are you assuming the user(s) will not enter there anyway?

 

[cardosan]

HI Pieter,
some more background.
this is gonna be a template to collect data. The person can fill as many row has he needs in each category (also 0..) and when he fills col C he will have to fill (at least in theory) also the other cols. Idea is that once he input one row one is added so he can input as many as he wants

Responding to your questions:

Are you only interested in column C?

What is supposed to happen if C3 gets data and 'energy' becomes 3 rows and then C3 is cleared leaving 'energy' with 3 blank rows?

not really needed

Are you only interested in column C?

What is supposed to happen if C3 gets data and 'energy' becomes 3 rows and then C3 is cleared leaving 'energy' with 3 blank rows?

Since the person has to fill all the cols after entering the C one my assumption is that it is enough to look only into it, but maybe since the use case in which he might fill and delete the C col a few times exists as you pointed out and every time adding or adding/deleting the row might just mess things up safer to look at more cells in the same row i.e. add the row when he fills x cells in the same row (which should also avoid the issue of second point)

What happens if C4 is filled in before C3?

This is not an issue.

Is the code supposed to check/enforce nothing in that row or are you assuming the user(s) will not enter there anyway?

For now I am assuming user will not enter anything, franky I would like to enforce the user not to put anything but in this minisheet there is only one blank row but there can be several since user can insert as many activities as he whishes of different size (i.e rows)

 

[Peter_SSs]

See if this worksheet change code would suffice then

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim c As Range
  
  Set c = Intersect(Target, Columns("C"))
  If Not c Is Nothing Then
    If c.CountLarge = 1 Then
      If Len(c.Value) > 0 Then Rows(c.Row + 1).Insert
    End If
  End If
End Sub

 

[cardosan]

Thanks @Peter_SSs,
it actually work but there is one issue. It does not adds the row only when the cell is filled but also every time it is modified. E.g. when in an already filled cells the content is changed a row is added, which should not be.
Would also be nice to look not only at if 1 single cell is filled but adding a cell when multiple cells are filled.