CODEWITHSUNDEEP
vbaexcelrange
Vbasic4now
Vbasic4now

Reputation: 589

Excel vba clearing a range of data that is changing regularly

I am trying to make a simple vba function in excel that clears a range of data, that goes from column A to K and row 6 to the bottom of the data, by finding the last used row and setting that as the end of the range. This is the code I have that is not working:

Sub clearNameData()

Dim destSheet As Worksheet: Set destSheet = ThisWorkbook.Worksheets("Name Search")

lMaxRows = destSheet.Cells(destSheet.Rows.Count, "A").End(xlUp).Row

destSheet.range("A6:K & lMaxRows").ClearContents

End Sub

When I run this code it gives me the error "Run-time error '1004': MEthod 'Range' of object'_Worksheet' failed"

I cant figure out exactly how to get what im trying to do to work despite trying every syntax combination of my range definition. Any help would be greatly appreciated!

Upvotes: 1

Views: 140

Answers (1)

Joe
Joe

Reputation: 170

As requested by the OP, I'm moving my comment to the answer.

The issue was with the line 

destSheet.range("A6:K & lMaxRows").ClearContents

It needed to be changed to: 

destSheet.range("A6:K" & lMaxRows).ClearContents

Note that the ending quotation mark moved. Supposing that lMaxRows is 500, this makes the range argument "A6:K500", which VBA can then evaluate as a range object. Before, it was the literal string A6:KlMaxRows, which VBA is not able to evaluate to a range object, since the variable is part of a string.

Upvotes: 4

Related Questions