Column Delete based on String
 

I'm desperately tring to delete a column if a cell within a range equals
"TESTING". Below gives error "Method 'Range' of Object '_Global' failed". I
also need this selection to auto set to A1 to last non-blank cell on row 1.
The cell coloring line works and is just there so I know I'm on the right
cell.

Any help in fixing these 2 actions may save a lot of gray hair.

Sub testSelectDelete()
Dim cell As Range

For Each cell In Selection
If Not IsEmpty(cell) And cell.Value = "TESTING" Then
cell.Interior.Color = RGB(255, 255, 192)
Range(cell).EntireColumn.Delete
End If
Next

End Sub

Column Delete based on String
 

Simply changing:

Range(cell).EntireColumn.Delete
to:
cell.EntireColumn.Delete

should do it.

RBS

"scott" wrote in message
...
I'm desperately tring to delete a column if a cell within a range equals
"TESTING". Below gives error "Method 'Range' of Object '_Global' failed".
I also need this selection to auto set to A1 to last non-blank cell on row
1. The cell coloring line works and is just there so I know I'm on the
right cell.

Any help in fixing these 2 actions may save a lot of gray hair.

Sub testSelectDelete()
Dim cell As Range

For Each cell In Selection
If Not IsEmpty(cell) And cell.Value = "TESTING" Then
cell.Interior.Color = RGB(255, 255, 192)
Range(cell).EntireColumn.Delete
End If
Next

End Sub

Column Delete based on String
 

Scott,

To sort the code you need

Sub testSelectDelete()
Dim cell As Range

For Each cell In Selection
If Not IsEmpty(cell) And cell.Value = "TESTING" Then
cell.Interior.Color = RGB(255, 255, 192)
cell.EntireColumn.Delete
End If
Next

End Sub

But why do you test for not empty and has a value of "TESTING" (the second
can only mean tat it isn't empty), and why do you set the cell's colour,
then delete the whole column (thereby deleting the cell you have just set)?


--
HTH

Bob Phillips

"scott" wrote in message
...
I'm desperately tring to delete a column if a cell within a range equals
"TESTING". Below gives error "Method 'Range' of Object '_Global' failed".
I
also need this selection to auto set to A1 to last non-blank cell on row
1.
The cell coloring line works and is just there so I know I'm on the right
cell.

Any help in fixing these 2 actions may save a lot of gray hair.

Sub testSelectDelete()
Dim cell As Range

For Each cell In Selection
If Not IsEmpty(cell) And cell.Value = "TESTING" Then
cell.Interior.Color = RGB(255, 255, 192)
Range(cell).EntireColumn.Delete
End If
Next

End Sub

Column Delete based on String
 

1. I only set the color of the cell for testing purposes. My code dies on
Delete line.
2. I don't need to sort, I need to ...
3. Set the range to test from A1 to last non-blank cell on Row 1
4. Find any cell with value of "TESTING" and if so, delete that column

Please help me with these steps, I've wasted 2 weekends now just trying to
get some basic moving around the sheet actions.


"Bob Phillips" wrote in message
...
Scott,

To sort the code you need

Sub testSelectDelete()
Dim cell As Range

For Each cell In Selection
If Not IsEmpty(cell) And cell.Value = "TESTING" Then
cell.Interior.Color = RGB(255, 255, 192)
cell.EntireColumn.Delete
End If
Next

End Sub

But why do you test for not empty and has a value of "TESTING" (the second
can only mean tat it isn't empty), and why do you set the cell's colour,
then delete the whole column (thereby deleting the cell you have just
set)?


--
HTH

Bob Phillips

"scott" wrote in message
...
I'm desperately tring to delete a column if a cell within a range equals
"TESTING". Below gives error "Method 'Range' of Object '_Global' failed".
I
also need this selection to auto set to A1 to last non-blank cell on row
1.
The cell coloring line works and is just there so I know I'm on the right
cell.

Any help in fixing these 2 actions may save a lot of gray hair.

Sub testSelectDelete()
Dim cell As Range

For Each cell In Selection
If Not IsEmpty(cell) And cell.Value = "TESTING" Then
cell.Interior.Color = RGB(255, 255, 192)
Range(cell).EntireColumn.Delete
End If
Next

End Sub

Column Delete based on String
 

Sub DeleteEmptyRows()
Dim LastCol As Long
Dim r As Long
LastCol = Range("IV1").End(xltoLeft).Column
Application.ScreenUpdating = False
For r = LastCol To 1 Step -1
If Application.CountIF(Columns(r),"TESTING") < 0 _
Then Columns(r).Delete
Next r
Application.ScreenUpdating = True
End Sub

--
Regards,
Tom Ogilvy


"scott" wrote in message
...
1. I only set the color of the cell for testing purposes. My code dies on
Delete line.
2. I don't need to sort, I need to ...
3. Set the range to test from A1 to last non-blank cell on Row 1
4. Find any cell with value of "TESTING" and if so, delete that column

Please help me with these steps, I've wasted 2 weekends now just trying to
get some basic moving around the sheet actions.


"Bob Phillips" wrote in message
...
Scott,

To sort the code you need

Sub testSelectDelete()
Dim cell As Range

For Each cell In Selection
If Not IsEmpty(cell) And cell.Value = "TESTING" Then
cell.Interior.Color = RGB(255, 255, 192)
cell.EntireColumn.Delete
End If
Next

End Sub

But why do you test for not empty and has a value of "TESTING" (the
second
can only mean tat it isn't empty), and why do you set the cell's colour,
then delete the whole column (thereby deleting the cell you have just
set)?


--
HTH

Bob Phillips

"scott" wrote in message
...
I'm desperately tring to delete a column if a cell within a range
equals
"TESTING". Below gives error "Method 'Range' of Object '_Global'
failed".
I
also need this selection to auto set to A1 to last non-blank cell on
row
1.
The cell coloring line works and is just there so I know I'm on the
right
cell.

Any help in fixing these 2 actions may save a lot of gray hair.

Sub testSelectDelete()
Dim cell As Range

For Each cell In Selection
If Not IsEmpty(cell) And cell.Value = "TESTING" Then
cell.Interior.Color = RGB(255, 255, 192)
Range(cell).EntireColumn.Delete
End If
Next

End Sub

Column Delete based on String
 

I think this should do what you want:

Sub Test()

Dim rngToTest As Range
Dim rngCell As Range

If IsEmpty(Cells(256)) Then
Set rngToTest = Range(Cells(1), Cells(256).End(xlToLeft))
Else
Set rngToTest = Rows(1).Cells
End If

Application.ScreenUpdating = False

For Each rngCell In rngToTest
If rngCell.Value = "TESTING" Then
rngCell.EntireColumn.Delete
End If
Next

Application.ScreenUpdating = True

End Sub


RBS


"scott" wrote in message
...
1. I only set the color of the cell for testing purposes. My code dies on
Delete line.
2. I don't need to sort, I need to ...
3. Set the range to test from A1 to last non-blank cell on Row 1
4. Find any cell with value of "TESTING" and if so, delete that column

Please help me with these steps, I've wasted 2 weekends now just trying to
get some basic moving around the sheet actions.


"Bob Phillips" wrote in message
...
Scott,

To sort the code you need

Sub testSelectDelete()
Dim cell As Range

For Each cell In Selection
If Not IsEmpty(cell) And cell.Value = "TESTING" Then
cell.Interior.Color = RGB(255, 255, 192)
cell.EntireColumn.Delete
End If
Next

End Sub

But why do you test for not empty and has a value of "TESTING" (the
second
can only mean tat it isn't empty), and why do you set the cell's colour,
then delete the whole column (thereby deleting the cell you have just
set)?


--
HTH

Bob Phillips

"scott" wrote in message
...
I'm desperately tring to delete a column if a cell within a range equals
"TESTING". Below gives error "Method 'Range' of Object '_Global'
failed".
I
also need this selection to auto set to A1 to last non-blank cell on row
1.
The cell coloring line works and is just there so I know I'm on the
right
cell.

Any help in fixing these 2 actions may save a lot of gray hair.

Sub testSelectDelete()
Dim cell As Range

For Each cell In Selection
If Not IsEmpty(cell) And cell.Value = "TESTING" Then
cell.Interior.Color = RGB(255, 255, 192)
Range(cell).EntireColumn.Delete
End If
Next

End Sub

Column Delete based on String
 

i tried your and RB's code and nothing happened. I just want to ...

1. Set range from A1 to last non empty cell on Row 1
2. Search that range for any cells containing "TESTING"
3. If a cell is found within that range, delete the column it resides


"Tom Ogilvy" wrote in message
...
Sub DeleteEmptyRows()
Dim LastCol As Long
Dim r As Long
LastCol = Range("IV1").End(xltoLeft).Column
Application.ScreenUpdating = False
For r = LastCol To 1 Step -1
If Application.CountIF(Columns(r),"TESTING") < 0 _
Then Columns(r).Delete
Next r
Application.ScreenUpdating = True
End Sub

--
Regards,
Tom Ogilvy


"scott" wrote in message
...
1. I only set the color of the cell for testing purposes. My code dies on
Delete line.
2. I don't need to sort, I need to ...
3. Set the range to test from A1 to last non-blank cell on Row 1
4. Find any cell with value of "TESTING" and if so, delete that column

Please help me with these steps, I've wasted 2 weekends now just trying
to
get some basic moving around the sheet actions.


"Bob Phillips" wrote in message
...
Scott,

To sort the code you need

Sub testSelectDelete()
Dim cell As Range

For Each cell In Selection
If Not IsEmpty(cell) And cell.Value = "TESTING" Then
cell.Interior.Color = RGB(255, 255, 192)
cell.EntireColumn.Delete
End If
Next

End Sub

But why do you test for not empty and has a value of "TESTING" (the
second
can only mean tat it isn't empty), and why do you set the cell's
colour,
then delete the whole column (thereby deleting the cell you have just
set)?


--
HTH

Bob Phillips

"scott" wrote in message
...
I'm desperately tring to delete a column if a cell within a range
equals
"TESTING". Below gives error "Method 'Range' of Object '_Global'
failed".
I
also need this selection to auto set to A1 to last non-blank cell on
row
1.
The cell coloring line works and is just there so I know I'm on the
right
cell.

Any help in fixing these 2 actions may save a lot of gray hair.

Sub testSelectDelete()
Dim cell As Range

For Each cell In Selection
If Not IsEmpty(cell) And cell.Value = "TESTING" Then
cell.Interior.Color = RGB(255, 255, 192)
Range(cell).EntireColumn.Delete
End If
Next

End Sub

Column Delete based on String
 

When I said sort, I meant sort out!

--
HTH

Bob Phillips

"Bob Phillips" wrote in message
...
Scott,

To sort the code you need

Sub testSelectDelete()
Dim cell As Range

For Each cell In Selection
If Not IsEmpty(cell) And cell.Value = "TESTING" Then
cell.Interior.Color = RGB(255, 255, 192)
cell.EntireColumn.Delete
End If
Next

End Sub

But why do you test for not empty and has a value of "TESTING" (the second
can only mean tat it isn't empty), and why do you set the cell's colour,
then delete the whole column (thereby deleting the cell you have just
set)?


--
HTH

Bob Phillips

"scott" wrote in message
...
I'm desperately tring to delete a column if a cell within a range equals
"TESTING". Below gives error "Method 'Range' of Object '_Global'
failed".
I
also need this selection to auto set to A1 to last non-blank cell on row
1.
The cell coloring line works and is just there so I know I'm on the
right
cell.

Any help in fixing these 2 actions may save a lot of gray hair.

Sub testSelectDelete()
Dim cell As Range

For Each cell In Selection
If Not IsEmpty(cell) And cell.Value = "TESTING" Then
cell.Interior.Color = RGB(255, 255, 192)
Range(cell).EntireColumn.Delete
End If
Next

End Sub

Column Delete based on String
 

It works with me.
Did you have the exact string TESTING in row 1?

RBS

"scott" wrote in message
...
i tried your and RB's code and nothing happened. I just want to ...

1. Set range from A1 to last non empty cell on Row 1
2. Search that range for any cells containing "TESTING"
3. If a cell is found within that range, delete the column it resides


"Tom Ogilvy" wrote in message
...
Sub DeleteEmptyRows()
Dim LastCol As Long
Dim r As Long
LastCol = Range("IV1").End(xltoLeft).Column
Application.ScreenUpdating = False
For r = LastCol To 1 Step -1
If Application.CountIF(Columns(r),"TESTING") < 0 _
Then Columns(r).Delete
Next r
Application.ScreenUpdating = True
End Sub

--
Regards,
Tom Ogilvy


"scott" wrote in message
...
1. I only set the color of the cell for testing purposes. My code dies
on
Delete line.
2. I don't need to sort, I need to ...
3. Set the range to test from A1 to last non-blank cell on Row 1
4. Find any cell with value of "TESTING" and if so, delete that column

Please help me with these steps, I've wasted 2 weekends now just trying
to
get some basic moving around the sheet actions.


"Bob Phillips" wrote in message
...
Scott,

To sort the code you need

Sub testSelectDelete()
Dim cell As Range

For Each cell In Selection
If Not IsEmpty(cell) And cell.Value = "TESTING" Then
cell.Interior.Color = RGB(255, 255, 192)
cell.EntireColumn.Delete
End If
Next

End Sub

But why do you test for not empty and has a value of "TESTING" (the
second
can only mean tat it isn't empty), and why do you set the cell's
colour,
then delete the whole column (thereby deleting the cell you have just
set)?


--
HTH

Bob Phillips

"scott" wrote in message
...
I'm desperately tring to delete a column if a cell within a range
equals
"TESTING". Below gives error "Method 'Range' of Object '_Global'
failed".
I
also need this selection to auto set to A1 to last non-blank cell on
row
1.
The cell coloring line works and is just there so I know I'm on the
right
cell.

Any help in fixing these 2 actions may save a lot of gray hair.

Sub testSelectDelete()
Dim cell As Range

For Each cell In Selection
If Not IsEmpty(cell) And cell.Value = "TESTING" Then
cell.Interior.Color = RGB(255, 255, 192)
Range(cell).EntireColumn.Delete
End If
Next

End Sub

Column Delete based on String
 

Worked fine for me. Testing was not in the first row. If it was anywhere
in the column, the column was deleted. In my case, testing was the only
value in the cell. If you want any cell that has Testing anywhere in it.

this deletes the column if anywhere in it it contains the substring testing.


Sub DeleteEmptyRows()
Dim LastCol As Long
Dim r As Long
LastCol = Range("IV1").End(xltoLeft).Column
Application.ScreenUpdating = False
For r = LastCol To 1 Step -1
If Application.CountIF(Columns(r),"*TESTING*") < 0 _
Then Columns(r).Delete
Next r
Application.ScreenUpdating = True
End Sub

If you want to restrict to row 1 only

Sub DeleteEmptyRows()
Dim LastCol As Long
Dim r As Long
LastCol = Range("IV1").End(xltoLeft).Column
Application.ScreenUpdating = False
For r = LastCol To 1 Step -1
If Application.CountIF(Cells(1,r) ,"*TESTING*") < 0 _
Then Columns(r).Delete
Next r
Application.ScreenUpdating = True
End Sub



--
Regards,
Tom Ogilvy


"scott" wrote in message
...
i tried your and RB's code and nothing happened. I just want to ...

1. Set range from A1 to last non empty cell on Row 1
2. Search that range for any cells containing "TESTING"
3. If a cell is found within that range, delete the column it resides


"Tom Ogilvy" wrote in message
...
Sub DeleteEmptyRows()
Dim LastCol As Long
Dim r As Long
LastCol = Range("IV1").End(xltoLeft).Column
Application.ScreenUpdating = False
For r = LastCol To 1 Step -1
If Application.CountIF(Columns(r),"TESTING") < 0 _
Then Columns(r).Delete
Next r
Application.ScreenUpdating = True
End Sub

--
Regards,
Tom Ogilvy


"scott" wrote in message
...
1. I only set the color of the cell for testing purposes. My code dies
on
Delete line.
2. I don't need to sort, I need to ...
3. Set the range to test from A1 to last non-blank cell on Row 1
4. Find any cell with value of "TESTING" and if so, delete that column

Please help me with these steps, I've wasted 2 weekends now just trying
to
get some basic moving around the sheet actions.


"Bob Phillips" wrote in message
...
Scott,

To sort the code you need

Sub testSelectDelete()
Dim cell As Range

For Each cell In Selection
If Not IsEmpty(cell) And cell.Value = "TESTING" Then
cell.Interior.Color = RGB(255, 255, 192)
cell.EntireColumn.Delete
End If
Next

End Sub

But why do you test for not empty and has a value of "TESTING" (the
second
can only mean tat it isn't empty), and why do you set the cell's
colour,
then delete the whole column (thereby deleting the cell you have just
set)?


--
HTH

Bob Phillips

"scott" wrote in message
...
I'm desperately tring to delete a column if a cell within a range
equals
"TESTING". Below gives error "Method 'Range' of Object '_Global'
failed".
I
also need this selection to auto set to A1 to last non-blank cell on
row
1.
The cell coloring line works and is just there so I know I'm on the
right
cell.

Any help in fixing these 2 actions may save a lot of gray hair.

Sub testSelectDelete()
Dim cell As Range

For Each cell In Selection
If Not IsEmpty(cell) And cell.Value = "TESTING" Then
cell.Interior.Color = RGB(255, 255, 192)
Range(cell).EntireColumn.Delete
End If
Next

End Sub

Column Delete based on String
 

I got it finally, my dumb ass had "testing" in J2. I've been looking at this
too long.




"Tom Ogilvy" wrote in message
...
Worked fine for me. Testing was not in the first row. If it was anywhere
in the column, the column was deleted. In my case, testing was the only
value in the cell. If you want any cell that has Testing anywhere in it.

this deletes the column if anywhere in it it contains the substring
testing.


Sub DeleteEmptyRows()
Dim LastCol As Long
Dim r As Long
LastCol = Range("IV1").End(xltoLeft).Column
Application.ScreenUpdating = False
For r = LastCol To 1 Step -1
If Application.CountIF(Columns(r),"*TESTING*") < 0 _
Then Columns(r).Delete
Next r
Application.ScreenUpdating = True
End Sub

If you want to restrict to row 1 only

Sub DeleteEmptyRows()
Dim LastCol As Long
Dim r As Long
LastCol = Range("IV1").End(xltoLeft).Column
Application.ScreenUpdating = False
For r = LastCol To 1 Step -1
If Application.CountIF(Cells(1,r) ,"*TESTING*") < 0 _
Then Columns(r).Delete
Next r
Application.ScreenUpdating = True
End Sub



--
Regards,
Tom Ogilvy


"scott" wrote in message
...
i tried your and RB's code and nothing happened. I just want to ...

1. Set range from A1 to last non empty cell on Row 1
2. Search that range for any cells containing "TESTING"
3. If a cell is found within that range, delete the column it resides


"Tom Ogilvy" wrote in message
...
Sub DeleteEmptyRows()
Dim LastCol As Long
Dim r As Long
LastCol = Range("IV1").End(xltoLeft).Column
Application.ScreenUpdating = False
For r = LastCol To 1 Step -1
If Application.CountIF(Columns(r),"TESTING") < 0 _
Then Columns(r).Delete
Next r
Application.ScreenUpdating = True
End Sub

--
Regards,
Tom Ogilvy


"scott" wrote in message
...
1. I only set the color of the cell for testing purposes. My code dies
on
Delete line.
2. I don't need to sort, I need to ...
3. Set the range to test from A1 to last non-blank cell on Row 1
4. Find any cell with value of "TESTING" and if so, delete that column

Please help me with these steps, I've wasted 2 weekends now just
trying
to
get some basic moving around the sheet actions.


"Bob Phillips" wrote in message
...
Scott,

To sort the code you need

Sub testSelectDelete()
Dim cell As Range

For Each cell In Selection
If Not IsEmpty(cell) And cell.Value = "TESTING" Then
cell.Interior.Color = RGB(255, 255, 192)
cell.EntireColumn.Delete
End If
Next

End Sub

But why do you test for not empty and has a value of "TESTING" (the
second
can only mean tat it isn't empty), and why do you set the cell's
colour,
then delete the whole column (thereby deleting the cell you have
just
set)?


--
HTH

Bob Phillips

"scott" wrote in message
...
I'm desperately tring to delete a column if a cell within a range
equals
"TESTING". Below gives error "Method 'Range' of Object '_Global'
failed".
I
also need this selection to auto set to A1 to last non-blank cell
on
row
1.
The cell coloring line works and is just there so I know I'm on the
right
cell.

Any help in fixing these 2 actions may save a lot of gray hair.

Sub testSelectDelete()
Dim cell As Range

For Each cell In Selection
If Not IsEmpty(cell) And cell.Value = "TESTING" Then
cell.Interior.Color = RGB(255, 255, 192)
Range(cell).EntireColumn.Delete
End If
Next

End Sub

Column Delete based on String
 

I don't understand the line

LastCol = Range("IV1").End(xltoLeft).Column

Can you explain what that means?


"Tom Ogilvy" wrote in message
...
Worked fine for me. Testing was not in the first row. If it was anywhere
in the column, the column was deleted. In my case, testing was the only
value in the cell. If you want any cell that has Testing anywhere in it.

this deletes the column if anywhere in it it contains the substring
testing.


Sub DeleteEmptyRows()
Dim LastCol As Long
Dim r As Long
LastCol = Range("IV1").End(xltoLeft).Column
Application.ScreenUpdating = False
For r = LastCol To 1 Step -1
If Application.CountIF(Columns(r),"*TESTING*") < 0 _
Then Columns(r).Delete
Next r
Application.ScreenUpdating = True
End Sub

If you want to restrict to row 1 only

Sub DeleteEmptyRows()
Dim LastCol As Long
Dim r As Long
LastCol = Range("IV1").End(xltoLeft).Column
Application.ScreenUpdating = False
For r = LastCol To 1 Step -1
If Application.CountIF(Cells(1,r) ,"*TESTING*") < 0 _
Then Columns(r).Delete
Next r
Application.ScreenUpdating = True
End Sub



--
Regards,
Tom Ogilvy


"scott" wrote in message
...
i tried your and RB's code and nothing happened. I just want to ...

1. Set range from A1 to last non empty cell on Row 1
2. Search that range for any cells containing "TESTING"
3. If a cell is found within that range, delete the column it resides


"Tom Ogilvy" wrote in message
...
Sub DeleteEmptyRows()
Dim LastCol As Long
Dim r As Long
LastCol = Range("IV1").End(xltoLeft).Column
Application.ScreenUpdating = False
For r = LastCol To 1 Step -1
If Application.CountIF(Columns(r),"TESTING") < 0 _
Then Columns(r).Delete
Next r
Application.ScreenUpdating = True
End Sub

--
Regards,
Tom Ogilvy


"scott" wrote in message
...
1. I only set the color of the cell for testing purposes. My code dies
on
Delete line.
2. I don't need to sort, I need to ...
3. Set the range to test from A1 to last non-blank cell on Row 1
4. Find any cell with value of "TESTING" and if so, delete that column

Please help me with these steps, I've wasted 2 weekends now just
trying
to
get some basic moving around the sheet actions.


"Bob Phillips" wrote in message
...
Scott,

To sort the code you need

Sub testSelectDelete()
Dim cell As Range

For Each cell In Selection
If Not IsEmpty(cell) And cell.Value = "TESTING" Then
cell.Interior.Color = RGB(255, 255, 192)
cell.EntireColumn.Delete
End If
Next

End Sub

But why do you test for not empty and has a value of "TESTING" (the
second
can only mean tat it isn't empty), and why do you set the cell's
colour,
then delete the whole column (thereby deleting the cell you have
just
set)?


--
HTH

Bob Phillips

"scott" wrote in message
...
I'm desperately tring to delete a column if a cell within a range
equals
"TESTING". Below gives error "Method 'Range' of Object '_Global'
failed".
I
also need this selection to auto set to A1 to last non-blank cell
on
row
1.
The cell coloring line works and is just there so I know I'm on the
right
cell.

Any help in fixing these 2 actions may save a lot of gray hair.

Sub testSelectDelete()
Dim cell As Range

For Each cell In Selection
If Not IsEmpty(cell) And cell.Value = "TESTING" Then
cell.Interior.Color = RGB(255, 255, 192)
Range(cell).EntireColumn.Delete
End If
Next

End Sub

Column Delete based on String
 

Exactly my suspicion!

RBS


"scott" wrote in message
...
I got it finally, my dumb ass had "testing" in J2. I've been looking at
this too long.




"Tom Ogilvy" wrote in message
...
Worked fine for me. Testing was not in the first row. If it was
anywhere
in the column, the column was deleted. In my case, testing was the only
value in the cell. If you want any cell that has Testing anywhere in it.

this deletes the column if anywhere in it it contains the substring
testing.


Sub DeleteEmptyRows()
Dim LastCol As Long
Dim r As Long
LastCol = Range("IV1").End(xltoLeft).Column
Application.ScreenUpdating = False
For r = LastCol To 1 Step -1
If Application.CountIF(Columns(r),"*TESTING*") < 0 _
Then Columns(r).Delete
Next r
Application.ScreenUpdating = True
End Sub

If you want to restrict to row 1 only

Sub DeleteEmptyRows()
Dim LastCol As Long
Dim r As Long
LastCol = Range("IV1").End(xltoLeft).Column
Application.ScreenUpdating = False
For r = LastCol To 1 Step -1
If Application.CountIF(Cells(1,r) ,"*TESTING*") < 0 _
Then Columns(r).Delete
Next r
Application.ScreenUpdating = True
End Sub



--
Regards,
Tom Ogilvy


"scott" wrote in message
...
i tried your and RB's code and nothing happened. I just want to ...

1. Set range from A1 to last non empty cell on Row 1
2. Search that range for any cells containing "TESTING"
3. If a cell is found within that range, delete the column it resides


"Tom Ogilvy" wrote in message
...
Sub DeleteEmptyRows()
Dim LastCol As Long
Dim r As Long
LastCol = Range("IV1").End(xltoLeft).Column
Application.ScreenUpdating = False
For r = LastCol To 1 Step -1
If Application.CountIF(Columns(r),"TESTING") < 0 _
Then Columns(r).Delete
Next r
Application.ScreenUpdating = True
End Sub

--
Regards,
Tom Ogilvy


"scott" wrote in message
...
1. I only set the color of the cell for testing purposes. My code
dies
on
Delete line.
2. I don't need to sort, I need to ...
3. Set the range to test from A1 to last non-blank cell on Row 1
4. Find any cell with value of "TESTING" and if so, delete that
column

Please help me with these steps, I've wasted 2 weekends now just
trying
to
get some basic moving around the sheet actions.


"Bob Phillips" wrote in message
...
Scott,

To sort the code you need

Sub testSelectDelete()
Dim cell As Range

For Each cell In Selection
If Not IsEmpty(cell) And cell.Value = "TESTING" Then
cell.Interior.Color = RGB(255, 255, 192)
cell.EntireColumn.Delete
End If
Next

End Sub

But why do you test for not empty and has a value of "TESTING" (the
second
can only mean tat it isn't empty), and why do you set the cell's
colour,
then delete the whole column (thereby deleting the cell you have
just
set)?


--
HTH

Bob Phillips

"scott" wrote in message
...
I'm desperately tring to delete a column if a cell within a range
equals
"TESTING". Below gives error "Method 'Range' of Object '_Global'
failed".
I
also need this selection to auto set to A1 to last non-blank cell
on
row
1.
The cell coloring line works and is just there so I know I'm on
the
right
cell.

Any help in fixing these 2 actions may save a lot of gray hair.

Sub testSelectDelete()
Dim cell As Range

For Each cell In Selection
If Not IsEmpty(cell) And cell.Value = "TESTING" Then
cell.Interior.Color = RGB(255, 255, 192)
Range(cell).EntireColumn.Delete
End If
Next

End Sub

Column Delete based on String
 

It finds the last filled cell in row1.

--
Regards,
Tom Ogilvy

"scott" wrote in message
...
I don't understand the line

LastCol = Range("IV1").End(xltoLeft).Column

Can you explain what that means?


"Tom Ogilvy" wrote in message
...
Worked fine for me. Testing was not in the first row. If it was
anywhere
in the column, the column was deleted. In my case, testing was the only
value in the cell. If you want any cell that has Testing anywhere in
it.

this deletes the column if anywhere in it it contains the substring
testing.


Sub DeleteEmptyRows()
Dim LastCol As Long
Dim r As Long
LastCol = Range("IV1").End(xltoLeft).Column
Application.ScreenUpdating = False
For r = LastCol To 1 Step -1
If Application.CountIF(Columns(r),"*TESTING*") < 0 _
Then Columns(r).Delete
Next r
Application.ScreenUpdating = True
End Sub

If you want to restrict to row 1 only

Sub DeleteEmptyRows()
Dim LastCol As Long
Dim r As Long
LastCol = Range("IV1").End(xltoLeft).Column
Application.ScreenUpdating = False
For r = LastCol To 1 Step -1
If Application.CountIF(Cells(1,r) ,"*TESTING*") < 0 _
Then Columns(r).Delete
Next r
Application.ScreenUpdating = True
End Sub



--
Regards,
Tom Ogilvy


"scott" wrote in message
...
i tried your and RB's code and nothing happened. I just want to ...

1. Set range from A1 to last non empty cell on Row 1
2. Search that range for any cells containing "TESTING"
3. If a cell is found within that range, delete the column it resides


"Tom Ogilvy" wrote in message
...
Sub DeleteEmptyRows()
Dim LastCol As Long
Dim r As Long
LastCol = Range("IV1").End(xltoLeft).Column
Application.ScreenUpdating = False
For r = LastCol To 1 Step -1
If Application.CountIF(Columns(r),"TESTING") < 0 _
Then Columns(r).Delete
Next r
Application.ScreenUpdating = True
End Sub

--
Regards,
Tom Ogilvy


"scott" wrote in message
...
1. I only set the color of the cell for testing purposes. My code
dies
on
Delete line.
2. I don't need to sort, I need to ...
3. Set the range to test from A1 to last non-blank cell on Row 1
4. Find any cell with value of "TESTING" and if so, delete that
column

Please help me with these steps, I've wasted 2 weekends now just
trying
to
get some basic moving around the sheet actions.


"Bob Phillips" wrote in message
...
Scott,

To sort the code you need

Sub testSelectDelete()
Dim cell As Range

For Each cell In Selection
If Not IsEmpty(cell) And cell.Value = "TESTING" Then
cell.Interior.Color = RGB(255, 255, 192)
cell.EntireColumn.Delete
End If
Next

End Sub

But why do you test for not empty and has a value of "TESTING"
(the
second
can only mean tat it isn't empty), and why do you set the cell's
colour,
then delete the whole column (thereby deleting the cell you have
just
set)?


--
HTH

Bob Phillips

"scott" wrote in message
...
I'm desperately tring to delete a column if a cell within a range
equals
"TESTING". Below gives error "Method 'Range' of Object '_Global'
failed".
I
also need this selection to auto set to A1 to last non-blank cell
on
row
1.
The cell coloring line works and is just there so I know I'm on
the
right
cell.

Any help in fixing these 2 actions may save a lot of gray hair.

Sub testSelectDelete()
Dim cell As Range

For Each cell In Selection
If Not IsEmpty(cell) And cell.Value = "TESTING" Then
cell.Interior.Color = RGB(255, 255, 192)
Range(cell).EntireColumn.Delete
End If
Next

End Sub

Column Delete based on String
 

Hi
Found this thread very interestiing. Am complete novice at programming,
have tried this coding and it works for me, but I cannot then re-instate the
columns. Is it possible to HIDE the columns, which can be then be un-hidden?

Thanks

"scott" wrote:

I'm desperately tring to delete a column if a cell within a range equals
"TESTING". Below gives error "Method 'Range' of Object '_Global' failed". I
also need this selection to auto set to A1 to last non-blank cell on row 1.
The cell coloring line works and is just there so I know I'm on the right
cell.

Any help in fixing these 2 actions may save a lot of gray hair.

Sub testSelectDelete()
Dim cell As Range

For Each cell In Selection
If Not IsEmpty(cell) And cell.Value = "TESTING" Then
cell.Interior.Color = RGB(255, 255, 192)
Range(cell).EntireColumn.Delete
End If
Next

End Sub

Column Delete based on String
 

Sub testSelectDelete()
Dim cell As Range

For Each cell In Selection
If Not IsEmpty(cell) And cell.Value = "TESTING" Then
cell.Interior.Color = RGB(255, 255, 192)
Range(cell).EntireColumn.Hidden = True
End If
Next

End Sub

--
Regards,
Tom Ogilvy

"Cattle Annie" wrote in message
...
Hi
Found this thread very interestiing. Am complete novice at programming,
have tried this coding and it works for me, but I cannot then re-instate
the
columns. Is it possible to HIDE the columns, which can be then be
un-hidden?

Thanks

"scott" wrote:

I'm desperately tring to delete a column if a cell within a range equals
"TESTING". Below gives error "Method 'Range' of Object '_Global'
failed". I
also need this selection to auto set to A1 to last non-blank cell on row
1.
The cell coloring line works and is just there so I know I'm on the
right
cell.

Any help in fixing these 2 actions may save a lot of gray hair.

Sub testSelectDelete()
Dim cell As Range

For Each cell In Selection
If Not IsEmpty(cell) And cell.Value = "TESTING" Then
cell.Interior.Color = RGB(255, 255, 192)
Range(cell).EntireColumn.Delete
End If
Next

End Sub

Column Delete based on String
 

Hi Tom
It doesn't like the last line Range etc.
Also I don't need the colouring bit, can this line be deleted?
Thanks Annie

"Tom Ogilvy" wrote:

Sub testSelectDelete()
Dim cell As Range

For Each cell In Selection
If Not IsEmpty(cell) And cell.Value = "TESTING" Then
cell.Interior.Color = RGB(255, 255, 192)
Range(cell).EntireColumn.Hidden = True
End If
Next

End Sub

--
Regards,
Tom Ogilvy

"Cattle Annie" wrote in message
...
Hi
Found this thread very interestiing. Am complete novice at programming,
have tried this coding and it works for me, but I cannot then re-instate
the
columns. Is it possible to HIDE the columns, which can be then be
un-hidden?

Thanks

"scott" wrote:

I'm desperately tring to delete a column if a cell within a range equals
"TESTING". Below gives error "Method 'Range' of Object '_Global'
failed". I
also need this selection to auto set to A1 to last non-blank cell on row
1.
The cell coloring line works and is just there so I know I'm on the
right
cell.

Any help in fixing these 2 actions may save a lot of gray hair.

Sub testSelectDelete()
Dim cell As Range

For Each cell In Selection
If Not IsEmpty(cell) And cell.Value = "TESTING" Then
cell.Interior.Color = RGB(255, 255, 192)
Range(cell).EntireColumn.Delete
End If
Next

End Sub

Column Delete based on String
 

I thought you said it was working?

Sub testSelectDelete()
Dim cell As Range
' selection.EntireRow.Hidden = False
For Each cell In Selection
If Not IsEmpty(cell) And cell.Value = "TESTING" Then
cell.EntireColumn.Hidden = True
End If
Next

End Sub

Coloring is gone. If you want to undo paste hiding, uncomment the
' selection.EntireRow.Hidden = False

--
Regards,
Tom Ogilvy



"Cattle Annie" wrote in message
...
Hi Tom
It doesn't like the last line Range etc.
Also I don't need the colouring bit, can this line be deleted?
Thanks Annie

"Tom Ogilvy" wrote:

Sub testSelectDelete()
Dim cell As Range

For Each cell In Selection
If Not IsEmpty(cell) And cell.Value = "TESTING" Then
cell.Interior.Color = RGB(255, 255, 192)
Range(cell).EntireColumn.Hidden = True
End If
Next

End Sub

--
Regards,
Tom Ogilvy

"Cattle Annie" wrote in message
...
Hi
Found this thread very interestiing. Am complete novice at
programming,
have tried this coding and it works for me, but I cannot then
re-instate
the
columns. Is it possible to HIDE the columns, which can be then be
un-hidden?

Thanks

"scott" wrote:

I'm desperately tring to delete a column if a cell within a range
equals
"TESTING". Below gives error "Method 'Range' of Object '_Global'
failed". I
also need this selection to auto set to A1 to last non-blank cell on
row
1.
The cell coloring line works and is just there so I know I'm on the
right
cell.

Any help in fixing these 2 actions may save a lot of gray hair.

Sub testSelectDelete()
Dim cell As Range

For Each cell In Selection
If Not IsEmpty(cell) And cell.Value = "TESTING" Then
cell.Interior.Color = RGB(255, 255, 192)
Range(cell).EntireColumn.Delete
End If
Next

End Sub

Column Delete based on String
 

Hi Tom
The 'Delete' worked when I tried the original, but not the hide version!
Thanks
Annie


"Tom Ogilvy" wrote:

I thought you said it was working?

Sub testSelectDelete()
Dim cell As Range
' selection.EntireRow.Hidden = False
For Each cell In Selection
If Not IsEmpty(cell) And cell.Value = "TESTING" Then
cell.EntireColumn.Hidden = True
End If
Next

End Sub

Coloring is gone. If you want to undo paste hiding, uncomment the
' selection.EntireRow.Hidden = False

--
Regards,
Tom Ogilvy



"Cattle Annie" wrote in message
...
Hi Tom
It doesn't like the last line Range etc.
Also I don't need the colouring bit, can this line be deleted?
Thanks Annie

"Tom Ogilvy" wrote:

Sub testSelectDelete()
Dim cell As Range

For Each cell In Selection
If Not IsEmpty(cell) And cell.Value = "TESTING" Then
cell.Interior.Color = RGB(255, 255, 192)
Range(cell).EntireColumn.Hidden = True
End If
Next

End Sub

--
Regards,
Tom Ogilvy

"Cattle Annie" wrote in message
...
Hi
Found this thread very interestiing. Am complete novice at
programming,
have tried this coding and it works for me, but I cannot then
re-instate
the
columns. Is it possible to HIDE the columns, which can be then be
un-hidden?

Thanks

"scott" wrote:

I'm desperately tring to delete a column if a cell within a range
equals
"TESTING". Below gives error "Method 'Range' of Object '_Global'
failed". I
also need this selection to auto set to A1 to last non-blank cell on
row
1.
The cell coloring line works and is just there so I know I'm on the
right
cell.

Any help in fixing these 2 actions may save a lot of gray hair.

Sub testSelectDelete()
Dim cell As Range

For Each cell In Selection
If Not IsEmpty(cell) And cell.Value = "TESTING" Then
cell.Interior.Color = RGB(255, 255, 192)
Range(cell).EntireColumn.Delete
End If
Next

End Sub

Column Delete based on String
 

Thanks Tom
Works OK, appreciate your help
Annie

"Tom Ogilvy" wrote:

I thought you said it was working?

Sub testSelectDelete()
Dim cell As Range
' selection.EntireRow.Hidden = False
For Each cell In Selection
If Not IsEmpty(cell) And cell.Value = "TESTING" Then
cell.EntireColumn.Hidden = True
End If
Next

End Sub

Coloring is gone. If you want to undo paste hiding, uncomment the
' selection.EntireRow.Hidden = False

--
Regards,
Tom Ogilvy



"Cattle Annie" wrote in message
...
Hi Tom
It doesn't like the last line Range etc.
Also I don't need the colouring bit, can this line be deleted?
Thanks Annie

"Tom Ogilvy" wrote:

Sub testSelectDelete()
Dim cell As Range

For Each cell In Selection
If Not IsEmpty(cell) And cell.Value = "TESTING" Then
cell.Interior.Color = RGB(255, 255, 192)
Range(cell).EntireColumn.Hidden = True
End If
Next

End Sub

--
Regards,
Tom Ogilvy

"Cattle Annie" wrote in message
...
Hi
Found this thread very interestiing. Am complete novice at
programming,
have tried this coding and it works for me, but I cannot then
re-instate
the
columns. Is it possible to HIDE the columns, which can be then be
un-hidden?

Thanks

"scott" wrote:

I'm desperately tring to delete a column if a cell within a range
equals
"TESTING". Below gives error "Method 'Range' of Object '_Global'
failed". I
also need this selection to auto set to A1 to last non-blank cell on
row
1.
The cell coloring line works and is just there so I know I'm on the
right
cell.

Any help in fixing these 2 actions may save a lot of gray hair.

Sub testSelectDelete()
Dim cell As Range

For Each cell In Selection
If Not IsEmpty(cell) And cell.Value = "TESTING" Then
cell.Interior.Color = RGB(255, 255, 192)
Range(cell).EntireColumn.Delete
End If
Next

End Sub