Variable Frustration!

Something like:

Please Login or Register  to view this content.

ws is the variable name. You could use almost anything you like to name this - but you don't want to discuss variable naming convention

It is dimensioned as a variant, so that we can assign any sort of value to it, and use the "For Each" method (can only be used with Variants or Objects). In this case, we are passing string values from an array, each of which is a worksheet name.

For Each means that each element of the array will be assigned to the variable in turn, then the actions between For Each... and Next... will be performed.

HTH

@Olly: a worksheet is an object

So you could also have:

Please Login or Register  to view this content.

Regards, TMS

There's nothing wrong with Olly's code. It works perfectly, as is, and could be dropped straight in and executed without change.

What did you change?

Please Login or Register  to view this content.

Well I managed to find the first error... I had misspelled a Sheet name ><

But now it's having me debug at the line that says ".Value = .Value and I got an error message saying "Excel cannot complete this task with available resources. Choose less data or close other applications." My two guesses are 1) I'm using too many sheets (I'd be surprised if that were the reason, though. 2) Some of the tables on my sheets are blank.

Any thoughts?

UPDATE: I filled in all tables and it twerked!

Thanks Olly!!!

If there's nothing in column J, it would be operating on over 10 million cells.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

Not solved yet, I have more questions for you geniuses!

The second part of my Macro is to delete columns K:AB on the same sheets. Would the following code work?

Dim vDeleteColumns As Variant
For Each vDeleteColumns In Array("Sheet1", "Sheet2")
With Worksheets(vDeleteColumns)
With .Range("K:AB")
Selection.Delete
End With
End With
Next vDeleteColumns

Selection.Delete doesn't work. I am unsurprised. I suppose it's because I never actually told it that K:AB should be selected

1. Please use CODE tags when posting code.
2. You don't need to select ranges to work with them. It slows everything down.
3. You need to specify exactly what it is you want to delete - in this case, it's the EntireColumns:

Please Login or Register  to view this content.