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

[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

 

[Gord Dibben]

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

 

[joel]

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

 

[Peter T]

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

 

[andreashermle]

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 -

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

 

[andreashermle]

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

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

 

[andreashermle]

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 -

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

 

[andreashermle]

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 -

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

 

[Gord Dibben]

You must have Option Explicit at top of module.

Add a line after Public Sub DeleteRowOnCell()

Dim coltocheck as Range

Aplogies for that.


Gord

 

[andreashermle]

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 -

ok, thank you very much for your terrific support