Deleting a row if column K is blank
 

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

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

Deleting a row if column K is blank
 

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?


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by
step guide to creating financial statements
.

Deleting a row if column K is blank
 

try this
Sub noblanks()
Range("k1:k17").SpecialCells(xlBlanks).Delete
End Sub

--
Don Guillett
SalesAid Software

"andycharger" wrote in 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?


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements

Deleting a row if column K is blank
 

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.


--
Regards,
Tom Ogilvy



"Patrick Molloy" wrote in message
...
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?


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by
step guide to creating financial statements
.