ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Excel 2k - A bug or bad vba...? (https://www.excelbanter.com/excel-programming/326943-excel-2k-bug-bad-vba.html)

toedipper
Excel 2k - A bug or bad vba...?
 
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
Excel 2k - A bug or bad vba...?
 
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" wrote in message
...
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
Excel 2k - A bug or bad vba...?
 
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" wrote in message
...
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" wrote in message
...
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[_3_]
Excel 2k - A bug or bad vba...?
 
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
Excel 2k - A bug or bad vba...?
 
....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" wrote in message
...
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


All times are GMT +1. The time now is 05:31 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com