selection.delete skips a column
 

HI:
I have a macro that loops through an excel spreadsheet and deletes certain
columns that I don't want. The problem I notice is that it skips columns and
only deletes every other column. In other words, if it is supposed to delete
20 columns it will actually only delete columns 1,3,5,7,9 etc. Skipping every
other column. Why would this be happening? Is it a problem with the offset?
Enclosed is my code.
TIA

Public Sub CoStar_Delete_Columns()
'Option Explicit
'select certain columns that I want to delete
Range("a1").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value = "Description Text #1" Or ActiveCell.Value =
"Description Text #2" _
Or ActiveCell.Value = "Description Text #3" Or ActiveCell.Value =
"Description Text #4" Or ActiveCell.Value = "Description Text #5" _
Or ActiveCell.Value = "Description Text #7" Or ActiveCell.Value =
"Description Text #8" Or ActiveCell.Value = "Description Text #9" _
Or ActiveCell.Value = "Description Text #10" Or ActiveCell.Value =
"Description Text #11" Or ActiveCell.Value = "Description Text #12" _
Or ActiveCell.Value = "Description Text #13" Or ActiveCell.Value =
"Description Text #14" Or ActiveCell.Value = "Description Text #15" _
Or ActiveCell.Value = "Description Text #16" Or ActiveCell.Value =
"Description Text #17" Or ActiveCell.Value = "Description Text #18" _
Or ActiveCell.Value = "Description Text #19" Or ActiveCell.Value =
"Description Text #20" Or ActiveCell.Value = "Dock High" Then
Columns(ActiveCell.Column).Select
Selection.Delete shift:=xlshiftright
End If

ActiveCell.Offset(, 1).Select
Loop

End Sub

selection.delete skips a column
 

try moving the offset line:

else
ActiveCell.Offset(, 1).Select

End If

classic issue of looping through a range while deleting a row or
columncontaining the activecell......as you delete it changes the "activecell"


"Geographer" wrote:

HI:
I have a macro that loops through an excel spreadsheet and deletes certain
columns that I don't want. The problem I notice is that it skips columns and
only deletes every other column. In other words, if it is supposed to delete
20 columns it will actually only delete columns 1,3,5,7,9 etc. Skipping every
other column. Why would this be happening? Is it a problem with the offset?
Enclosed is my code.
TIA

Public Sub CoStar_Delete_Columns()
'Option Explicit
'select certain columns that I want to delete
Range("a1").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value = "Description Text #1" Or ActiveCell.Value =
"Description Text #2" _
Or ActiveCell.Value = "Description Text #3" Or ActiveCell.Value =
"Description Text #4" Or ActiveCell.Value = "Description Text #5" _
Or ActiveCell.Value = "Description Text #7" Or ActiveCell.Value =
"Description Text #8" Or ActiveCell.Value = "Description Text #9" _
Or ActiveCell.Value = "Description Text #10" Or ActiveCell.Value =
"Description Text #11" Or ActiveCell.Value = "Description Text #12" _
Or ActiveCell.Value = "Description Text #13" Or ActiveCell.Value =
"Description Text #14" Or ActiveCell.Value = "Description Text #15" _
Or ActiveCell.Value = "Description Text #16" Or ActiveCell.Value =
"Description Text #17" Or ActiveCell.Value = "Description Text #18" _
Or ActiveCell.Value = "Description Text #19" Or ActiveCell.Value =
"Description Text #20" Or ActiveCell.Value = "Dock High" Then
Columns(ActiveCell.Column).Select
Selection.Delete shift:=xlshiftright
End If

ActiveCell.Offset(, 1).Select
Loop

End Sub

selection.delete skips a column
 

Geographer:

I think your problem was that you xlShiftRight, then immediately
Offset(,1). That is two columns. I rewrote the procedure and ran it.
It worked for me. Hope it does what you want it to do. Just
CopyNPaste =========

Public Sub CoStarDeleteColumns()

Dim Cntr As Integer
Dim StrDesc As String

Do Until ActiveCell = ""

For Cntr = 1 To 20

StrDesc = "Description Text #" & Cntr

If ActiveCell.Value = StrDesc Or _
ActiveCell.Value = "Dock High" Then

DelCnt = DelCnt + 1
ActiveCell.Columns.Delete
Cntr = 20

End If

Next Cntr

Loop

End Sub

Lemme know if that worked. Mark