Thanks, that works GREAT!!!!
Just one extra thing. Is there a way that we can delete the column set for
deletion and the next column? The reason being is when there are several
deleted names in a row it leaves the empty columns, which stack up, so
sometimes there is one empty column and sometimes there are more. it isn't
that bad on this sheet, but when I do similar code for other sheets there
will be more deletion than not.
Thanks again.
"Tom Ogilvy" wrote:
>
> Sub OfficeNameDelete()
> Dim i As Long
> Dim lastCol as Long
> With Sheets("Office")
> lastcol = .cells(1,"IV").End(xltoLeft).column
> For i = lastcol to 1 step -1
> if len(trim(.cells(1,i))) <> 0 then
> if
> application.Countif(worksheets("List").Columns(1),.cells(1,i)) = 0 then
> .columns(i).Delete
> end if
> end if
> Next
> end With
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Kevin Porter" wrote:
>
> > Ok. That makes a lot of sense, with the range continuously changing. It
> > makes my result make sense.
> >
> > The code you gave me goes down column A on the first worksheet, when I need
> > it to go across row 1. Then compare it to values in the second worksheet in
> > Column A.
> >
> > I have played with it a little bit, but it is still doing the same thing. I
> > am looking it up in the book I bought at lunch, but any help would be
> > appreciated.
> >
> > Thanks again for taking the time to help a novice.
> >
> > "Jim Thomlinson" wrote:
> >
> > > This should be close. Traversing a range of cells and deleteing rows within
> > > that range is problematic. You are tring to move through a range that keeps
> > > on changing. The code that I am posting basically creates a single range to
> > > be deleted at the end.
> > >
> > > Sub DeleteStuff()
> > > Dim rng As Range
> > > Dim rngDelete As Range
> > >
> > > Set rng = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(-1, 0)
> > > Do While rng.Row > 1
> > > If Application.CountIf(Sheets("Sheet2").Columns(1), rng.Value) = 0
> > > Then
> > > If rngDelete Is Nothing Then
> > > Set rngDelete = rng
> > > Else
> > > Set rngDelete = Union(rng, rngDelete)
> > > End If
> > > End If
> > > Set rng = rng.Offset(-1, 0)
> > > Loop
> > > If Not rngDelete Is Nothing Then rngDelete.EntireRow.Delete
> > > End Sub
> > >
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "Kevin Porter" wrote:
> > >
> > > > The following code is supposed to compare all the names in row 1 with a list
> > > > on another worksheet. If the name is in the list it continues on, if the
> > > > name is not in the list it deletes the column, if the cell is empty (NULL)
> > > > then I want it to ignore it and go to the next cell.
> > > >
> > > > As the code is now it deletes a couple of NULL cell columns at the begnning
> > > > of the worksheet, doesn't delete a few more, then deletes a couple of names
> > > > (properly as they are not on the list), then after it doesn't delete a column
> > > > (properly again because the name is on the list) it just deletes all the NULL
> > > > columns throughtout the rest of the row and doesn't delete anymore names.
> > > >
> > > > The Null columns between names is to make the spreadsheet much easier to
> > > > view, so I want to keep them, I just want the program to ignore them and move
> > > > on.
> > > >
> > > >
> > > > Dim iRow As String
> > > > Dim cell As Range
> > > > Sub OfficeNameDelete()
> > > >
> > > > With Sheets("Office")
> > > > For Each cell In .Range("$1:$1")
> > > > iRow = 0
> > > > On Error Resume Next
> > > > iRow = Application.VLookup(cell, Sheets("List").Range("$A:$B"), 1)
> > > > On Error GoTo 0
> > > > If cell = "TOTAL" Then
> > > > End
> > > > ElseIf cell = Null Then
> > > > Resume Next
> > > > ElseIf iRow <> cell Then
> > > > cell.EntireColumn.Delete
> > > > End If
> > > > Next cell
> > > > End With
> > > > End Sub
> > > >
> > > > Thanks in advance. I am off to buy a VBA for dummies book now.
|
Similar Threads
