Deleting a row if column K is blank

  • Thread starter Thread starter andycharger
  • Start date Start date
A

andycharger

Hi.
Im new to VB in Excel! Man its real different to pure VB!

What I want to do is cycle trough a spreadsheet and check column k fo
a blank space (i.e "")
If it is blank space, I want to delete the entire row.

Im not sure how I move through the entire spreadsheet either.
Obviously when doing it in Access you can use:
Do while not spreadsheet.EOF


Can someone help me
 
Sub DeleteBlankRows()
Dim Target As Range

Set Target = _
Sheet1.Range("K:K").SpecialCells(xlCellTypeBlanks)

Target.EntireRow.Delete


End Sub

Instead of K:K you could use a more defined range

Patrick Molloy
Microsft Excel MVP

-----Original Message-----

Hi.
Im new to VB in Excel! Man its real different to pure VB!

What I want to do is cycle trough a spreadsheet and check column k for
a blank space (i.e "")
If it is blank space, I want to delete the entire row.

Im not sure how I move through the entire spreadsheet either.
Obviously when doing it in Access you can use:
Do while not spreadsheet.EOF


Can someone help me?


------------------------------------------------

~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by
Click to expand...
step guide to creating financial statements
 
try this
Sub noblanks()
Range("k1:k17").SpecialCells(xlBlanks).Delete
End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)
andycharger said:
Hi.
Im new to VB in Excel! Man its real different to pure VB!

What I want to do is cycle trough a spreadsheet and check column k for
a blank space (i.e "")
If it is blank space, I want to delete the entire row.

Im not sure how I move through the entire spreadsheet either.
Obviously when doing it in Access you can use:
Do while not spreadsheet.EOF


Can someone help me?


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
Just to add that if there are no blank cells in Column K, you will get an
error with specialcells.

It is usually advisable to use error trapping when using specialcells

Sub DeleteBlankRows()
Dim Target As Range
On Error Resume Next
Set Target = _
Sheet1.Range("K:K").SpecialCells(xlCellTypeBlanks)
On Error goto 0
if not Target is nothing then _
Target.EntireRow.Delete


End Sub

Excel has a variable that describes the used region of the worksheet.

Activesheet.UsedRange

This defines the rectangular area of cells that Excel considers to be used
and which it stores information about. (the remainder of the cells are
essentually "virtual"). This will always include cells containing data, but
may include additional cells that have a custom format applied - and so this
area may overstate what you would consider to be actually in use. For data
that is organized like a data base, you can refer to it with currentregion.
Range("A1").CurrentRegion

if you want to loop through cells in either you can do

Dim cell as Range
for each cell in Range("A1").CurrentRegion

or

For each cell in Activesheet.UsedRange

as examples.
 
Back
Top