PDA

View Full Version : VBA code for inserting blank row in between the data points



shekar
11-03-2012, 08:01 AM
Hi All,

I have huge excel file which contains lot of data , i want find particular data point and insert blnk row above the data point.
my data as follows

1 Sales Order
2 minute
3 Two
4 second
5 Three
6 Sales Order
7 minute
8 Two
9 second
10 Three
11 Sales Order
12 minute
13 Two
14 second
15 Three
16 Sales Order
17 minute
18 Two
19 second
20 Three

here i want find Sales order and above that i want insert blank row ......i have 15000 data points like this..

GreenDR
11-06-2012, 12:07 PM
rcount = Application.WorksheetFunction.CountA(Range("A:A")) 'get the number of rows

For r = 1 To rcount 'for each row
If Range("A" & r).Value = "Sales Order" Then 'check for your criteria
Application.CutCopyMode = False 'clear the clipboard just to make sure you dont insert some data
Rows(r & ":" & r).Select 'select the row
Selection.Insert Shift:=xlDown 'insert row
r = r + 1
End If
Next r

macropod
11-06-2012, 10:24 PM
Just an observation:
Application.WorksheetFunction.CountA(Range("A:A"))
will not return a true row count where there are already blank rows (eg because the macro has been run before). Try:
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
or:
ActiveSheet.Range("A" & .Cells.SpecialCells(xlCellTypeLastCell).Row).End(xlUp).Row