Trying to create a custom "clear worksheet" function (in a VBA module)

  • Thread starter Thread starter Scott Lyon
  • Start date Start date
S

Scott Lyon

I need to create a function that will, given a worksheet (passed byRef), and
given a start row, that will clear all of the cells from column D, and the
given row, all the way down (and right) to the bottom of the data. Then it
will select the first cell (column D, and the given starting row) and end
the function.

I've got something like that created, but every time I try to get it to
select from the first cell to the last, I get a "Application-defined or
object-defined error".

What am I missing?


The code is as follows:



Sub ClearWorksheet(ByRef ws As Worksheet, ByVal intStartRow As Integer)
Dim tempString As String

tempString = "D" & CStr(intStartRow)
ws.Range(tempString,
Application.ActiveCell.SpecialCells(xlLastCell)).Select ' This is the line
giving me the Application-defined error

Selection.ClearContents
ws.Range("D" & CStr(intStartRow)).Select

End Sub



Thanks!
-Scott
 
Oh, one more thing I forgot to add. The reason I need this custom "clear
worksheet" function/sub (rather than using built-in functions to clear the
whole worksheet), is that I have quite a bit of header information that goes
in the left-most 3 columns, as well as the first 11-30 lines (it varies how
many, depending on the worksheet). If I clear the whole sheet, I'd lose all
of that (which I do not wish to do).


Hope this clears it up a little.

-Scott
 
Back
Top