PDA

View Full Version : Solved: insert row to Seperate Data Groups



Pete
11-24-2008, 11:05 AM
Hi

I have a macro alreday in place but cannot figure out how to add the following step.

Is it possible to insert a row after each highlighted country header in columns b.

so starting at row 9 column space between egypt data and finland then,

a space between finland data and Kazakhstan.....

Bob Phillips
11-24-2008, 11:45 AM
Where's the code? What you gave does nothing.

Pete
11-24-2008, 12:00 PM
hay!!!

is it not in module 1.....ok my mistake then here it is

Sub specialmacro()
Dim rng As Range
Dim str1 As String
Dim str2 As String
Dim rowe As Long
rowe = 5
str1 = "B"
str2 = "B"
With Sheets("Sheet1")
Set rng = Range(.Cells(rowe, str1), .Cells(.Cells.Rows.Count, str2).End(xlUp))
End With
rng.SpecialCells(xlCellTypeBlanks).Select
Selection.Rows.EntireRow.Delete
End Sub

Bob Phillips
11-24-2008, 12:17 PM
That is the code, but as I said it does nothing, it errors.

Are you simply asking for code to insert those blanks, I thought you wanted it added to some code that yoou had.

Pete
11-24-2008, 12:39 PM
I thought you wanted it added to some code that you had. - yes i do or a seperate bit of code will do to i can call it.

Pete
11-25-2008, 12:39 AM
can this be done?

Pete
11-25-2008, 01:34 AM
ok here is my shot at the code......but i keep on getting a compile error....


Sub specialmacro2()
Dim adr As String
Range("B5").Select
adr = Range(Selection, Selection.End(xlDown)).Address(RowAbsolute:=False, ColumnAbsolute:=False)
For Each c In Range(adr).Cells
If c.Offset(RowOffset:=0, ColumnOffset:=-1).Value = "" Then
c.Clear
End If
Next c
End Sub

Bob Phillips
11-25-2008, 01:37 AM
Sub specialmacro()
Dim rng As Range
Dim LastRow As Long
Dim i As Long

Application.ScreenUpdating = False

With Worksheets("Sheet1")

LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
Set rng = .Range("A6").Resize(LastRow - 5)
Set rng = rng.SpecialCells(xlCellTypeBlanks)
For i = LastRow To 6 Step -1

If .Cells(i, "A").Value = "" Then

.Rows(i).Insert
End If
Next i
End With

Application.ScreenUpdating = True
End Sub

Pete
11-25-2008, 02:17 AM
ok i see the error...

thanks xld once again fro your feedback...