Excel VBA: how to clear a selection without activating the sheet

Edit:

Not to clear the data in the selection, rather to remove the selection itself. I suppose this would be equivalent to selecting a different range, such as A1..

1

2 Answers

erm, you can always use VBA to reference the worksheet and cell range?

Option Explicit
Sub RemoveValues() Worksheets("ACME_Sales").Range("A1:B17").ClearContents
End Sub

This will clear off cells A1 to B17 in the worksheet ACME_Sales.

EDITED Edit answer to reflect edited question.

Sub SelectNewCell() Worksheets("ACME_Sales").Select Range("E2:E2").Select
End Sub

This will let VBA remove any selection on ACME_Sales and then just select the cell E2.

2

For a listrange you can all so make it like this

Sub test() ClearListRange sht, Range("PIVOTTABLEDATASOURCESSTART")
End Sub
Public Sub ClearListRange(sht as worksheet, Startrange As Range) sht.Range(Range(Startrange, Startrange.End(xlToRight)), Range(Startrange, Startrange.End(xlDown))).ClearContents
End Sub

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy