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

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

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

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).EntireRo w.Delete

End Sub


Gord Dibben MS Excel MVP

On Mon, 31 May 2010 08:52:37 -0700 (PDT), andreashermle
wrote:

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

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

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/sh...d.php?t=206269

http://www.thecodecage.com/forumz

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

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

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

On May 31, 6:27*pm, Gord Dibben <gorddibbATshawDOTca wrote:
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).EntireRo w.Delete

End Sub

Gord Dibben *MS Excel MVP

On Mon, 31 May 2010 08:52:37 -0700 (PDT), andreashermle



wrote:
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- Hide quoted text -

- 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

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

On May 31, 6:27*pm, joel wrote:
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/sh...d.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

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

On May 31, 6:28*pm, "Peter T" <peter_t@discussions wrote:
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" wrote in message

...



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- Hide quoted text -

- 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

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

On May 31, 6:27*pm, Gord Dibben <gorddibbATshawDOTca wrote:
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).EntireRo w.Delete

End Sub

Gord Dibben *MS Excel MVP

On Mon, 31 May 2010 08:52:37 -0700 (PDT), andreashermle



wrote:
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- Hide quoted text -

- 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

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

You must have Option Explicit at top of module.

Add a line after Public Sub DeleteRowOnCell()

Dim coltocheck as Range

Aplogies for that.


Gord

On Mon, 31 May 2010 20:54:02 -0700 (PDT), andreashermle
wrote:

On May 31, 6:27*pm, Gord Dibben <gorddibbATshawDOTca wrote:
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).EntireRo w.Delete

End Sub

Gord Dibben *MS Excel MVP

On Mon, 31 May 2010 08:52:37 -0700 (PDT), andreashermle



wrote:
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- Hide quoted text -

- 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

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

On 1 Jun., 16:41, Gord Dibben <gorddibbATshawDOTca wrote:
You must have Option Explicit at top of module.

Add a line after *Public Sub DeleteRowOnCell()

Dim coltocheck as Range

Aplogies for that.

Gord

On Mon, 31 May 2010 20:54:02 -0700 (PDT), andreashermle



wrote:
On May 31, 6:27*pm, Gord Dibben <gorddibbATshawDOTca wrote:
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).EntireRo w.Delete

End Sub

Gord Dibben *MS Excel MVP

On Mon, 31 May 2010 08:52:37 -0700 (PDT), andreashermle

wrote:
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- Hide quoted text -

- 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- Zitierten Text ausblenden -

- Zitierten Text anzeigen -

ok, thank you very much for your terrific support