Column Delete based on String

[scott]

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

 

[RB Smissaert]

Simply changing:

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

should do it.

RBS

 

[Bob Phillips]

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)?

 

[scott]

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.

 

[Tom Ogilvy]

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

 

[RB Smissaert]

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]

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

 

[Bob Phillips]

When I said sort, I meant sort out!

 

[RB Smissaert]

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

RBS

 

[Tom Ogilvy]

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

 

[scott]

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

 

[scott]

I don't understand the line

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

Can you explain what that means?

 

[RB Smissaert]

Exactly my suspicion!

RBS

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

 

[Tom Ogilvy]

It finds the last filled cell in row1.

 

[Guest]

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

 

[Tom Ogilvy]

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

 

[Guest]

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]

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

 

[Guest]

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

 

[Guest]

Thanks Tom
Works OK, appreciate your help
Annie

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