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

On Mon, 31 May 2010 08:52:37 -0700 (PDT), andreashermle
<(E-Mail Removed)> 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

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" <(E-Mail Removed)> wrote in message
news:268cb927-cea2-446f-9e00-(E-Mail Removed)...
> 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

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).EntireRow.Delete
>
> End Sub
>
> Gord Dibben *MS Excel MVP
>
> On Mon, 31 May 2010 08:52:37 -0700 (PDT), andreashermle
>
>
>
> <andreas.her...@gmx.de> 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

On May 31, 6:27*pm, joel <joel.4bu...@thecodecage.com> 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

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" <andreas.her...@gmx.de> wrote in message
>
> news:268cb927-cea2-446f-9e00-(E-Mail Removed)...
>
>
>
> > 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

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).EntireRow.Delete
>
> End Sub
>
> Gord Dibben *MS Excel MVP
>
> On Mon, 31 May 2010 08:52:37 -0700 (PDT), andreashermle
>
>
>
> <andreas.her...@gmx.de> 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

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
<(E-Mail Removed)> 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).EntireRow.Delete
>>
>> End Sub
>>
>> Gord Dibben *MS Excel MVP
>>
>> On Mon, 31 May 2010 08:52:37 -0700 (PDT), andreashermle
>>
>>
>>
>> <andreas.her...@gmx.de> 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

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
>
>
>
> <andreas.her...@gmx.de> 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).EntireRow.Delete
>
> >> End Sub
>
> >> Gord Dibben *MS Excel MVP
>
> >> On Mon, 31 May 2010 08:52:37 -0700 (PDT), andreashermle
>
> >> <andreas.her...@gmx.de> 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