Delete all rows where there is no entry in Column 'C'

  • Thread starter Thread starter andreashermle
  • Start date Start date
A

andreashermle

Dear Experts:

I got a multi-column, mulit-row table with numerous data records. Some
records do not have any entry in Column 'C'.

I now would like to be able via a macro to delete all records that do
not have any entry in Column C.

How is this achieved?

Thank you very much in advance for your great help.

Regards, Andreas
 
Public Sub DeleteRowOnCell()

Set coltocheck = Columns(3)

'if you want user-selectable column use inputbox

'Set coltocheck = Application.InputBox(prompt:= _
' "Select A Column", Type:=8)

coltocheck.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub


Gord Dibben MS Excel MVP
 
One way -
Add an autofilter to the top of column C
In the drop down select Blanks
Delete the filtered rows, if any
Remove the filter

Note though there must be at least one entry in each row of the filter
range, below which the filter will not work.

You could do that with a macro but it's barely worth it, and manually you
will keep your Undo stack.

Regards,
Peter T
 
Public Sub DeleteRowOnCell()

    Set coltocheck = Columns(3)

    'if you want user-selectable column use inputbox

   'Set coltocheck = Application.InputBox(prompt:= _
          '  "Select A Column", Type:=8)

    coltocheck.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub

Gord Dibben  MS Excel MVP








- Show quoted text -
Click to expand...

Hi Gord,

thank you very much for your swift help. I am afraid to tell you that
I am getting a compile error telling me that the variable 'coltocheck'
is not defined.

Regards, Andreas
 
try this

Sub Macro1()
'
With Sheets("sheet1")
LastRow = .Range("C" & Rows.Count).End(xlUp).Row
'use autofile to get blank items
.Columns("C:C").AutoFilter
.Columns("C:C").AutoFilter Field:=1, Criteria1:="="

'use special cells method to get visible rows, ones with blanks
Set VisibleRows = .Rows("1:" & LastRow) _
.SpecialCells(xlCellTypeVisible)
VisibleRows.Delete
'remove autofilter
.Cells.AutoFilter
End With
End Sub

--
joel
------------------------------------------------------------------------
joel's Profile:http://www.thecodecage.com/forumz/member.php?u=229
View this thread:http://www.thecodecage.com/forumz/showthread.php?t=206269

http://www.thecodecage.com/forumz
Click to expand...

Hi Joel,

it is working as desired. Thank you very much for your great help. I
had to delete the 'Option Explicit' statement in order not to get
constant error messages, that a variable has not been defined. What do
I have to do if I leave the 'Option Explicit' statement on its place.

Regards, Andreas
 
One way -
Add an autofilter to the top of column C
In the drop down select Blanks
Delete the filtered rows, if any
Remove the filter

Note though there must be at least one entry in each row of the filter
range, below which the filter will not work.

You could do that with a macro but it's barely worth it, and manually you
will keep your Undo stack.

Regards,
Peter T











- Show quoted text -
Click to expand...

Hey Peter,

ok, great thank you. One often forgets that there are very good built-
in functionalties. Now I got both approaches. That's very good.

Thank you very much for your professional help. Regards, Andreas
 
Public Sub DeleteRowOnCell()

    Set coltocheck = Columns(3)

    'if you want user-selectable column use inputbox

   'Set coltocheck = Application.InputBox(prompt:= _
          '  "Select A Column", Type:=8)

    coltocheck.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub

Gord Dibben  MS Excel MVP








- Show quoted text -
Click to expand...

Hi again,

as with joel's code, if I delete the 'Option Explicit' Statement, it
is running just fine. Thank you very much for your professional help.
Regards, Andreas
 
You must have Option Explicit at top of module.

Add a line after Public Sub DeleteRowOnCell()

Dim coltocheck as Range

Aplogies for that.


Gord
 
You must have Option Explicit at top of module.

Add a line after  Public Sub DeleteRowOnCell()

Dim coltocheck as Range

Aplogies for that.

Gord






- Zitierten Text anzeigen -
Click to expand...

ok, thank you very much for your terrific support
 
Back
Top