×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

WS.Columns

WS.Columns

WS.Columns

(OP)
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

CODE

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":

CODE

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

Any suggestions?

Many thanks,
D€$

RE: WS.Columns

How about some thing like...

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

Simi

RE: WS.Columns

Howdy,

This one line should suffice:

CODE

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.

RE: WS.Columns

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

I'd managed to get:-

CODE

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€$

RE: WS.Columns

CODE

'
    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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: WS.Columns

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

CODE

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

Many thanks,
D€$

RE: WS.Columns

Hmmmmmm? On the cusp, eh?

I thought you were going out the door. Was that a cuspidor? winky smile

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: WS.Columns

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

Many thanks,
D€$

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close