End selection in VBA

bradjedis
4StarLounger
Posts: 536
Joined: 30 Mar 2010, 18:49
Location: United States

End selection in VBA

Post by bradjedis »

Greetings,

I have the following: Works fine until it gets to the Selection.Unselect then macro fails. How do I end the selection?

Code: Select all

Sub ClearChecksJan()
'
' Macro1 Macro
'

'
    Range("F4:F25").Select
    Selection.ClearContents
    Selection.Unselect
End Sub

Thanks,
Brad

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: End selection in VBA

Post by StuartR »

Why are you selecting this range? Why not just

Code: Select all

Range("F4:F25").ClearContents
Excel always has at least one cell selected. You could change the selection to be just the one active cell (F4) with the command

Code: Select all

ActiveCell.Select
StuartR


User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: End selection in VBA

Post by HansV »

StuartR's suggestion

Range("F4:F25").ClearContents

has multiple advantages:
- It doesn't change the selection.
- If you have lots of such actions, it speeds up macro execution: selecting a range takes time.
Best wishes,
Hans

bradjedis
4StarLounger
Posts: 536
Joined: 30 Mar 2010, 18:49
Location: United States

Re: End selection in VBA

Post by bradjedis »

So I have a bill tracking spreadsheet. Delineated by month. Each row within each month has a cell for indicating if the bill has been paid. Each of those 12 columns has a Button to clear that set of cells in that column.
How would I implement the above suggestion. Right now I have 12 Subs. one for each month. Identical to my initial code above. only dif is the cell selection.

see attached image.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: End selection in VBA

Post by HansV »

You can assign the ClearChecksJan macro to each of the 12 buttons - you may want to rename it to ClearChecks. There is no need for a separate macro for each month.
Best wishes,
Hans

bradjedis
4StarLounger
Posts: 536
Joined: 30 Mar 2010, 18:49
Location: United States

Re: End selection in VBA

Post by bradjedis »

Ok so I see now. I actually like that better. I can select the individual cells to clear.


Thanks!