CODEWITHSUNDEEP
vbaexcelexcel-2010
IronKirby
IronKirby

Reputation: 708

Excel VBA Range Calculation

I'm working on creating a monthly consolidation function which doesn't like how I'm using this particular snippet of my code:

For Each ws In Worksheets

 If Len(ws.Name) <= 5 Then
    ws.Range("B7:C7").Select
    Selection.ClearContents
    Selection.AutoFill Destination:=Range("B7:C100"), Type:=xlFillDefault
    Range("B7:C100").Select

 End If

 Next

All client profiles are filled out via a template which self duplicates for each new individual so the profiles are all standardised. All user profiles are <=5 characters (acronyms). So the intent of my code is to check if the tab is <=5 characters, and then if it is - select the first range B7:C7 and clear this. Next we apply this cleared filter to all the other cells as well (down to 100).

I've defined ws as my Worksheet variable and I don't understand why I can't set the ws.range as B7:C7. I'd appreciate your insight!

I've attached a snapshot of what the form looks like.Snapshot of tool

Upvotes: 3

Views: 207

Answers (1)

paul bica
paul bica

Reputation: 10715

For Each ws In Worksheets
    If Len(ws.Name) <= 5 Then ws.Range("B7:C100").ClearContents
Next

Notes:

  • the .Select statement requires ws to be activated
  • you don't need to use .Select in this case (you can act on the range directly)

  • the .AutoFill is not bad but here is not needed

    • its usefulness is when incrementing initial data

Upvotes: 5

Related Questions