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 23:57 On May 31, 6:27 pm, joel <joel.4bu...(a)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/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
From: andreashermle on 31 May 2010 23:58 On May 31, 6:28 pm, "Peter T" <peter_t(a)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...(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- 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
From: andreashermle on 31 May 2010 23:59 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 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
From: Gord Dibben on 1 Jun 2010 10:41 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.hermle(a)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...(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
From: andreashermle on 2 Jun 2010 01:54
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...(a)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...(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- Zitierten Text ausblenden - > > - Zitierten Text anzeigen - ok, thank you very much for your terrific support |