Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
delete columns based on selection | Excel Discussion (Misc queries) | |||
Copy column header to next column, delete & delete every nth colum | New Users to Excel | |||
delete a selection | Charts and Charting in Excel | |||
Problem of selection.delete | Excel Worksheet Functions | |||
Copy Selection - Transpose Selection - Delete Selection | Excel Discussion (Misc queries) |