PDA

View Full Version : Solved: Running an application at each blank row.



pcarmour
12-23-2012, 02:46 PM
Hi,
I have a spread sheet with rows that are blank except for column "G" which has a formula. These rows are between various size groups of rows.
I want to select the cell in column "C" above each blank row and then run a VBA application called Add_Holdings ()
The data is across 15 columns some of which can be empty and about 150 rows.
I was working along the lines of:

With ActiveSheet.UsedRange
.AutoFilter Field:=1, Criteria1:=""
ActiveCell.Offset(-1, 2).Select
Application.Run "Add_Holdings"
.AutoFilter
End With

But nowhere near getting it to work.
I am working with Windows Home Premium version 6.1.7601 SP 1 Build7601and Excel version 14.0.6123.5001 (32 bit)
any help would be much appreciated

david000
12-23-2012, 06:23 PM
Sub a()
Dim rng As Range
Set rng = Intersect(Range("C:C"), ActiveSheet.UsedRange)
rng.SpecialCells(xlCellTypeBlanks).Offset(-1).Select
End Sub

mdmackillop
12-24-2012, 09:17 AM
To avoid error if C1 is blank
Set rng = Intersect(Range("C:C"), ActiveSheet.UsedRange).Offset(1)

pcarmour
12-24-2012, 09:33 AM
Thank you both, brilliant short code, this site is awesome!