Excel 2k - A bug or bad vba...?

toedipper · 04-06-2005, 07:06 PM

Hello,

Excel 2000 and Windows XP

I have some simple code which is supposed to go through 20 worksheets and
delete Column C in each sheet.. The code is below. Each sheet is filled
with content from cols A-O but instead of deleting col c in each sheet the
code below deletes whats in cols a-n in all sheets and puts what is in O in
col A.

Confusing I know but irritating. Can anyone tell me why the code below does
not just delete Col C in each sheet?

Thanks,

td.

Sub testdel()
'
' testdel Macro
' Macro recorded 06/04/2005 by blah blah
'
Sheets("RFC00020").Select
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft

Sheets("RFC00035").Select
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft

Sheets("RFC00040").Select
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft

Sheets("RFC00050").Select
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft

Sheets("RFC00060").Select
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft

Sheets("RFC00100").Select
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft

Sheets("RFC00101").Select
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft

Sheets("RFC00102").Select
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft

Sheets("RFC00103").Select
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft

Sheets("RFC00104").Select
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft

Sheets("RFC00199").Select
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft

Sheets("RFC00200").Select
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft

Sheets("RFC00201").Select
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft

Sheets("RFC00202").Select
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft

Sheets("RFC00203").Select
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft

Sheets("RFC00204").Select
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft

Sheets("RFC00299").Select
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft

Sheets("RFC00300").Select
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft

Sheets("RFC00303").Select
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft

Sheets("RFC00304").Select
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft

Sheets("RFC00403").Select
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft

Sheets("RFC00404").Select
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft

End Sub

Fredrik Wahlgren · 04-06-2005, 07:06 PM

Are there sheets where you don't want to delete column C? If not you can use
this shorter code
I can't see any reason for the described behaviour unless you have
accidentally run some other macro.

Public Sub testdel()
Dim iSheetCount As Integer
Dim iSheet As Integer

iSheetCount = ActiveWorkbook.Worksheets.Count

For iSheet = 1 To iSheetCount
Worksheets(iSheet).Activate
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Next iSheet
End Sub

/Fredrik

"toedipper" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> Excel 2000 and Windows XP
>
> I have some simple code which is supposed to go through 20 worksheets and
> delete Column C in each sheet.. The code is below. Each sheet is filled
> with content from cols A-O but instead of deleting col c in each sheet the
> code below deletes whats in cols a-n in all sheets and puts what is in O
in
> col A.
>
> Confusing I know but irritating. Can anyone tell me why the code below
does
> not just delete Col C in each sheet?
>
> Thanks,
>
> td.
>
> Sub testdel()
> '
> ' testdel Macro
> ' Macro recorded 06/04/2005 by blah blah
> '
> Sheets("RFC00020").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00035").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00040").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00050").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00060").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00100").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00101").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00102").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00103").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00104").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00199").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00200").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00201").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00202").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00203").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00204").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00299").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00300").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00303").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00304").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00403").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00404").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> End Sub
>
>

KL · 04-06-2005, 07:06 PM

you can further clean this code:

Public Sub testdel()
Dim i As Integer
With ActiveWorkbook
For i = 1 To .Worksheets.Count
Worksheets(iSheet).Columns("C:C").Delete Shift:=xlToLeft
Next i
End With
End Sub

If there were sheets to skip, you could do this:

Public Sub testdel()
Dim i As Integer
With ActiveWorkbook
For i = 1 To .Worksheets.Count
Select Case .Worksheets(i).Name
Case "Sheet1", "Sheet2", "Sheet33"
Case Else: .Worksheets(i).Columns("C:C").Delete Shift:=xlToLeft
End Select
Next i
End With
End Sub

Regards,
KL

"Fredrik Wahlgren" <[email protected]> wrote in message
news:[email protected]...
> Are there sheets where you don't want to delete column C? If not you can
> use
> this shorter code
> I can't see any reason for the described behaviour unless you have
> accidentally run some other macro.
>
> Public Sub testdel()
> Dim iSheetCount As Integer
> Dim iSheet As Integer
>
> iSheetCount = ActiveWorkbook.Worksheets.Count
>
> For iSheet = 1 To iSheetCount
> Worksheets(iSheet).Activate
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
> Next iSheet
> End Sub
>
> /Fredrik
>
> "toedipper" <[email protected]> wrote in message
> news:[email protected]...
>> Hello,
>>
>> Excel 2000 and Windows XP
>>
>> I have some simple code which is supposed to go through 20 worksheets and
>> delete Column C in each sheet.. The code is below. Each sheet is
>> filled
>> with content from cols A-O but instead of deleting col c in each sheet
>> the
>> code below deletes whats in cols a-n in all sheets and puts what is in O
> in
>> col A.
>>
>> Confusing I know but irritating. Can anyone tell me why the code below
> does
>> not just delete Col C in each sheet?
>>
>> Thanks,
>>
>> td.
>>
>> Sub testdel()
>> '
>> ' testdel Macro
>> ' Macro recorded 06/04/2005 by blah blah
>> '
>> Sheets("RFC00020").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00035").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00040").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00050").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00060").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00100").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00101").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00102").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00103").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00104").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00199").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00200").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00201").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00202").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00203").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00204").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00299").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00300").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00303").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00304").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00403").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00404").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> End Sub
>>
>>
>
>

Jim Thomlinson · 04-06-2005, 07:06 PM

I tried the code on 2K and I did not get the same problem... I did tighten
the code up for you though...

Sub testdel()
'
' testdel Macro
' Macro recorded 06/04/2005 by blah blah
'
Sheets("RFC00020").Columns("C:C").Delete Shift:=xlToLeft
Sheets("RFC00035").Columns("C:C").Delete Shift:=xlToLeft
Sheets("RFC00040").Columns("C:C").Delete Shift:=xlToLeft
Sheets("RFC00050").Columns("C:C").Delete Shift:=xlToLeft
Sheets("RFC00060").Columns("C:C").Delete Shift:=xlToLeft
Sheets("RFC00100").Columns("C:C").Delete Shift:=xlToLeft
Sheets("RFC00101").Columns("C:C").Delete Shift:=xlToLeft
Sheets("RFC00102").Columns("C:C").Delete Shift:=xlToLeft
Sheets("RFC00103").Columns("C:C").Delete Shift:=xlToLeft
Sheets("RFC00104").Columns("C:C").Delete Shift:=xlToLeft
Sheets("RFC00199").Columns("C:C").Delete Shift:=xlToLeft
Sheets("RFC00200").Columns("C:C").Delete Shift:=xlToLeft
Sheets("RFC00201").Columns("C:C").Delete Shift:=xlToLeft
Sheets("RFC00202").Columns("C:C").Delete Shift:=xlToLeft
Sheets("RFC00203").Columns("C:C").Delete Shift:=xlToLeft
Sheets("RFC00204").Columns("C:C").Delete Shift:=xlToLeft
Sheets("RFC00299").Columns("C:C").Delete Shift:=xlToLeft
Sheets("RFC00300").Columns("C:C").Delete Shift:=xlToLeft
Sheets("RFC00303").Columns("C:C").Delete Shift:=xlToLeft
Sheets("RFC00304").Columns("C:C").Delete Shift:=xlToLeft

End Sub

HTH

"toedipper" wrote:

> Hello,
>
> Excel 2000 and Windows XP
>
> I have some simple code which is supposed to go through 20 worksheets and
> delete Column C in each sheet.. The code is below. Each sheet is filled
> with content from cols A-O but instead of deleting col c in each sheet the
> code below deletes whats in cols a-n in all sheets and puts what is in O in
> col A.
>
> Confusing I know but irritating. Can anyone tell me why the code below does
> not just delete Col C in each sheet?
>
> Thanks,
>
> td.
>
> Sub testdel()
> '
> ' testdel Macro
> ' Macro recorded 06/04/2005 by blah blah
> '
> Sheets("RFC00020").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00035").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00040").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00050").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00060").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00100").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00101").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00102").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00103").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00104").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00199").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00200").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00201").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00202").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00203").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00204").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00299").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00300").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00303").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00304").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00403").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> Sheets("RFC00404").Select
> Columns("C:C").Select
> Selection.Delete Shift:=xlToLeft
>
> End Sub
>
>
>

KL · 04-06-2005, 08:07 PM

....some further tightenning:

Sub testdel()
Dim ws As Variant
ws = Array(20, 35, 40, 50, 60, 100, _
101, 102, 103, 104, 199, 200, 201, 202, _
203, 204, 299, 300, 303, 304)

For i = LBound(ws) To UBound(ws)
Sheets("RFC" & Format(ws(i), "00000")) _
.Columns("C:C").Delete Shift:=xlToLeft
Next i
End Sub

Regards,
KL

"Jim Thomlinson" <[email protected]> wrote in message
news:[email protected]...
>I tried the code on 2K and I did not get the same problem... I did tighten
> the code up for you though...
>
> Sub testdel()
> '
> ' testdel Macro
> ' Macro recorded 06/04/2005 by blah blah
> '
> Sheets("RFC00020").Columns("C:C").Delete Shift:=xlToLeft
> Sheets("RFC00035").Columns("C:C").Delete Shift:=xlToLeft
> Sheets("RFC00040").Columns("C:C").Delete Shift:=xlToLeft
> Sheets("RFC00050").Columns("C:C").Delete Shift:=xlToLeft
> Sheets("RFC00060").Columns("C:C").Delete Shift:=xlToLeft
> Sheets("RFC00100").Columns("C:C").Delete Shift:=xlToLeft
> Sheets("RFC00101").Columns("C:C").Delete Shift:=xlToLeft
> Sheets("RFC00102").Columns("C:C").Delete Shift:=xlToLeft
> Sheets("RFC00103").Columns("C:C").Delete Shift:=xlToLeft
> Sheets("RFC00104").Columns("C:C").Delete Shift:=xlToLeft
> Sheets("RFC00199").Columns("C:C").Delete Shift:=xlToLeft
> Sheets("RFC00200").Columns("C:C").Delete Shift:=xlToLeft
> Sheets("RFC00201").Columns("C:C").Delete Shift:=xlToLeft
> Sheets("RFC00202").Columns("C:C").Delete Shift:=xlToLeft
> Sheets("RFC00203").Columns("C:C").Delete Shift:=xlToLeft
> Sheets("RFC00204").Columns("C:C").Delete Shift:=xlToLeft
> Sheets("RFC00299").Columns("C:C").Delete Shift:=xlToLeft
> Sheets("RFC00300").Columns("C:C").Delete Shift:=xlToLeft
> Sheets("RFC00303").Columns("C:C").Delete Shift:=xlToLeft
> Sheets("RFC00304").Columns("C:C").Delete Shift:=xlToLeft
>
> End Sub
>
> HTH
>
> "toedipper" wrote:
>
>> Hello,
>>
>> Excel 2000 and Windows XP
>>
>> I have some simple code which is supposed to go through 20 worksheets and
>> delete Column C in each sheet.. The code is below. Each sheet is
>> filled
>> with content from cols A-O but instead of deleting col c in each sheet
>> the
>> code below deletes whats in cols a-n in all sheets and puts what is in O
>> in
>> col A.
>>
>> Confusing I know but irritating. Can anyone tell me why the code below
>> does
>> not just delete Col C in each sheet?
>>
>> Thanks,
>>
>> td.
>>
>> Sub testdel()
>> '
>> ' testdel Macro
>> ' Macro recorded 06/04/2005 by blah blah
>> '
>> Sheets("RFC00020").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00035").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00040").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00050").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00060").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00100").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00101").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00102").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00103").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00104").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00199").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00200").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00201").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00202").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00203").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00204").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00299").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00300").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00303").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00304").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00403").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> Sheets("RFC00404").Select
>> Columns("C:C").Select
>> Selection.Delete Shift:=xlToLeft
>>
>> End Sub
>>
>>
>>