Deleting Rows

  • Thread starter Thread starter Dwight
  • Start date Start date
D

Dwight

Hi all,

Iam a first time user of Excel (or any other spreadsheet). I have put
together a form for data input. All of the data input will be in Column "C".
I wish to test Column "C" for blank cells, and if blank delete that row.
This question maybe similar to the "automatic search and delete "question.
I don't know how to use a micro (still reading on that one ) or is there a
function that I can put at each location or should I? Thanks for any help.

Dwight
 
Hi Dwight
in your case change the macro to

Sub delete_rows()
Dim RowNdx As Long
Dim LastRow As Long

LastRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).row
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "C").Value = "" Then
Rows(RowNdx).Delete
End If
Next RowNdx
End Sub

for some instructions how to use this code you may have a look at
http://www.mvps.org/dmcritchie/excel/getstarted.htm

or try the following
- open your workbook
- hilt ALT-F11 to open the VBA editor
- paste the code from above
- close the VBA editor
- save your workbook
- goto 'Tools- Macro - Macros'
- start this macro
 
If you want to do it manually, then you can simply select the whole of Column C,
do Edit / Go To / Special / Blanks, then do Edit / Delete / Entire Row.

Another VBA routine besides Frank's would be:-

Sub DeleteBlankRows()
On Error Resume Next
Columns("C:C").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End Sub

To use this, simply hit ALT+F11 and this will open the VBE (Visual Basic Editor)
Top left you will hopefully see an explorer style pane. Within this pane you
need to search for
your workbook's name, and when you find it you may need to click on the + to
expand it. Within
that you should see the following:-

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
ThisWorkbook

If you have named your sheets then those names will appear in the brackets above
as opposed to
what you see at the moment in my note.

Right click on your project name and do Insert / Module, and hopefully it will
now look like this:-

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
ThisWorkbook
Modules
Module1

Doubleclick Module1 and a big white empty area will appear. Paste the macro
into there, do File / Close and return To Microsoft Excel, and then just use
Tools / Macro / Macros / DeleteBlankRows to run it.
 
Back
Top