Delete all rows where there is no entry in Column 'C'
in [Programming]
|
Prev: Alterations/corrections on userform
Next: simple resize
From: andreashermle on 31 May 2010 11:52 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
From: Gord Dibben on 31 May 2010 12:27 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.hermle(a)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
From: joel on 31 May 2010 12:27 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
From: Peter T on 31 May 2010 12:28 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.hermle(a)gmx.de> wrote in message news:268cb927-cea2-446f-9e00-44d44cb3ddef(a)f13g2000vbm.googlegroups.com... > 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
From: andreashermle on 31 May 2010 23:54
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...(a)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 |