Insert Row only above a row with a number in column 1

[ktmo]

We have a calculation sheet that exports a list of test treatments to Excel from another database. We then run a macro on it to insert formulas to calculate certain things for our lab work. There can be anywhere between 1 and 25 treatments usually.

When the data exports to excel, the treatment number is listed in column 1 (A). There might be multiple lines of data per treatment, but the treatment number is only printed once on the first line of that treatment.

When there are multiple treatments in the list, it is difficult to distinguish them from one another because there are no spaces between the different treatments. See below: (well, in the post it aligns everything to the left, but the numbers and the chems are in different columns)

1 chem 1
chem 2
chem 3
2 chem 1
chem 3
3 chem 2
chem 3
4 chem 3

Is there a way to insert a blank row above each row with a number in the first column? I think it would involve searching down until finding a populated cell and inserting a row. Somehow it needs to only do it for numbers though, because there is some text that prints out at the end of the treatment list. I've seen similar problems solved on the board but I can't figure out how to put them together to do this.
Help?

 

[VoG]

Try this

Sub AddBlanks()
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
    If Range("A" & i).Value <> "" Then Rows(i).Insert
Next i
End Sub

 

[ktmo]

Ok, that added rows above each number, but it also added rows above any row with anything in column 1 (A), not just numbers.

THe numbers ALWAYS start with 1 on ROW 18.

Is there anyway to run this only on or below ROW 18?

 

[VoG]

Try this

Sub AddBlanks()
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = LR To 19 Step -1
    If Range("A" & i).Value <> "" Then Rows(i).Insert
Next i
End Sub

 

[stanleydgromjr]

ktmo,

Before the macro:

Excel Workbook
	A	B
1	1	chem 1
2		chem 2
3		chem 3
4	2	chem 1
5		chem 3
6	3	chem 2
7		chem 3
8	4	chem 3
Sheet1

After the macro:

Excel Workbook
	A	B
1
2	1	chem 1
3		chem 2
4		chem 3
5
6	2	chem 1
7		chem 3
8
9	3	chem 2
10		chem 3
11
12	4	chem 3
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Press and hold down the 'ALT' key, and press the 'F11' key.

On the 'Insert' menu, click 'Module'.

Copy the below code, and paste it into the Module (on the right pane).

Option Explicit
Sub InsertRow()
    Dim LR As Long, Ctr As Long
    Application.ScreenUpdating = False
    With ActiveSheet
        LR = .Cells(Rows.Count, 2).End(xlUp).Row
        For Ctr = LR To 1 Step -1
            If .Cells(Ctr, 1).Value = 0 Then
                'Do nothing
            ElseIf IsNumeric(.Cells(Ctr, 1).Value) Then
                .Cells(Ctr, 1).EntireRow.Insert
            End If
        Next Ctr
    End With
    Application.ScreenUpdating = True
End Sub

Then run the "InsertRow" macro.


Have a great day,
Stan

 

[stanleydgromjr]

ktmo,

Is there anyway to run this only on or below ROW 18?

Option Explicit
Sub InsertRow()
    Dim LR As Long, Ctr As Long
    Application.ScreenUpdating = False
    With ActiveSheet
        LR = .Cells(Rows.Count, 2).End(xlUp).Row
        For Ctr = LR To 19 Step -1
            If .Cells(Ctr, 1).Value = 0 Then
                'Do nothing
            ElseIf IsNumeric(.Cells(Ctr, 1).Value) Then
                .Cells(Ctr, 1).EntireRow.Insert
            End If
        Next Ctr
    End With
    Application.ScreenUpdating = True
End Sub

Have a great day,
Stan

 

[ktmo]

Thank you! You all are ROCKSTARS!

This is the last bit of a much longer macro that takes a bunch of garble and calculates what we need then makes it nice and easy to read and print.

I have learned so much about VBA through this board. Thanks!