If data is in a row above, insert a new row above.

[Dagoom]

Hello, I am wondering if there is any way to have it so if there is any data in column B-F, it will keep an extra row between the values from Column A.

This would make it so that my I could always have a blank row between data about different jobs.

So if A5 has a job number, and B4-F4 has data in it, A row would be inserted above Row A5, making the cell that was in A5, now in A6.

However if B4-F4 is blank, it is left in A5 and not pushed down to A6.

It wouldn't matter what cell its the fourth row, just as soon as it does, a blank row is created.

Sorry if this is confusing at all, but I had no idea how to word all this.

[HaHoBe]

Hi, Dagoom,

maybe like this (assuming that data is filled in continuously from B to F for any row):

Sub EF965130()
Dim lngRC As Long
For lngRC = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
  If Cells(lngRC, "A").Value <> "" And WorksheetFunction.CountBlank(Range(Cells(lngRC - 1, "B"), Cells(lngRC - 1, "F"))) = 0 Then
    Rows(lngRC).Insert
  End If
Next lngRC
End Sub

Ciao,
Holger

[Dagoom]

It was pretty close to what I want, except it only inserted a blank in one spot, is there anyway to have it do multiple rows at the same time?

Also, it is not always continuous, it contains data about contact information and such, sometimes I will not have emails, so is there any way to have it so that if ANY of the cells are filled, it creats a blank row?

Also, is there any way to have this be automatic on a sheet? I tried to put it in the code area, but I still need to manually run it.

[HaHoBe]

Hi, Dagoom,

It was pretty close to what I wan

Good guess of mine withhout seeing your data and maybe noticing what might be wrong. As I mentioned before the code supplied assumes an entry into any column from B to F for the row which may cause the code to refuse to insert a new row where it should be. And I assumed values to be located in the cells, not formulas.

You could try the altered code and see if this works alright for you:

Sub EF965130_1()
Dim lngRC As Long
For lngRC = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
  If Cells(lngRC, "A").Value <> "" And WorksheetFunction.CountBlank(Range(Cells(lngRC - 1, "B"), Cells(lngRC - 1, "F"))) < 5 Then
    Rows(lngRC).Insert
  End If
Next lngRC
End Sub

Or maybe attach a workbook with a sample of where the code doesnŽt fulfill like wanted.

Automatism: depending on what event should be watched to trigger the event. IŽll start with the Worksheet_Actiate which will cause the code to run whenever the sheet is activated either by code or manually. Depending on whether the data is entered manually (Worksheet_Change) or imported by formula (Worksheet_Calculate) these events could be taken into consideration as well.

Ciao,
Holger

[Dagoom]

You hit the nail on the head there,

Thank you very much!

How would I go about making it so whenever a cell is edited, the macro would run?

Sorry, I am still kind of new to how excel works, and migrated from Quattro pro.

I really appreciate you taking your time to help me out.

Alright, I found out how to make it do what I wanted.

Private Sub Worksheet_Change(ByVal target As Range)
Dim lngRC As Long
For lngRC = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
  If Cells(lngRC, "A").Value <> "" And WorksheetFunction.CountBlank(Range(Cells(lngRC - 1, "B"), Cells(lngRC - 1, "F"))) < 5 Then
    Rows(lngRC).Insert
  End If
Next lngRC
End Sub

Thanks a bunch.