Excel column cell by cell, insert blank rows & find end of data 1

[JoanieB]

What command can I use to insert a blank line based on the value of a cell? Ie: Whenever I encounter a cell in my first column with the value "Main" I want to insert a row right before it. I want to loop throught the entire column of data, retaining the last row letter with data in it for reference. How can I loop through, inserting blank lines and what do I use to determine the where the data ends?

 

[pcdaveh]

Try this it works until the last line you'll have to tweak it slightly.

Option Explicit
Dim lRealLastRow As Integer
Dim lRealLastColumn As Integer
Public Sub GetRealLastCell()
Range("A1&quot.Select
On Error Resume Next
lRealLastRow = Cells.Find("*", Range("A1&quot, xlFormulas, , xlByRows, xlPrevious).Row
lRealLastColumn = Cells.Find("*", Range("A1&quot, xlFormulas, , xlByColumns, xlPrevious).Column
Cells(lRealLastRow, lRealLastColumn).Select
End Sub

Sub insertBlankline()
Dim ls_CellValue
Dim li_RowPosition

li_RowPosition = 1
Call GetRealLastCell
Do Until li_RowPosition >= lRealLastRow
Range("A" & li_RowPosition).Select
ls_CellValue = ActiveCell.Text

If ls_CellValue = "main" Then
ActiveCell.Insert
li_RowPosition = li_RowPosition + 2
lRealLastRow = lRealLastRow + 1
'Call GetRealLastCell
Else
li_RowPosition = li_RowPosition + 1
'Call GetRealLastCell
End If
Loop
End Sub

 

[JoanieB]

Thanks!
ps
I LOVE tweaking...ahahaha.