WS.Columns

[PWD]

Good afternoon, just revisiting VBA after some time away. I'm trying to cycle through worksheets in a workbook but I'm having trouble with a couple of things.

1) I want to replace this code with something less Selecty

    WS.Columns("K:K").Select
    WS.Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.EntireColumn.Delete

2) But unless each WS is active then this sort of code fails anyway.

I think I've found a way to use ws.columns in my "Replace":

    With WS.Columns("F:F")
    .Replace What:="DL", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    End With

Any suggestions?[tt][/tt]

Many thanks,
D€$

 

[simian336]

How about some thing like...

For Each Sheet In Sheets
Sheet.Range("A:A").Delete
Next

Simi

 

[MakeItSo]

Howdy,

This one line should suffice:

WS.Range("F:F").EntireColumn.Delete

Cheers,
MakeItSo

"Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family." (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[PWD]

Hi guys, thanks for this. Apologies for not explaining what I was trying to achieve - minutes before going home!

I'd managed to get:-

WS.Columns("A:C").Delete Shift:=xlToLeft

to do some column deletion, but what I'm trying to do with Columns "K" onwards is just to delete everything (all the columns) from there to the end of each worksheet.

Firstly the code I posted above only appears to act on the currently active worksheet - despite being "ws.", so each worksheet needs to be activated for this clunky code to work; and secondly I'd like to have code that didn't involve selecting the cells.



Many thanks,
D€$

 

[SkipVought]

'
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
    'delete columns from column K rightward
       With ws
          .Range(.Cells(1, "K"), .Cells(1, .Columns.Count)).EntireColumn.Delete xlLeft
       End With
    Next

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[PWD]

Thanks Skip, I was just on the cusp of trying:

.Range(.Columns(11), .Columns(LastCol)).Delete Shift:=xlToLeft

Many thanks,
D€$

 

[SkipVought]

Hmmmmmm? On the cusp, eh?

I thought you were going out the door. Was that a cuspidor? ;-)

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[PWD]

Ha ha. That was last Thursday. I've got about anther 2½ hrs to go today.

Many thanks,
D€$