Question: Clear Data within Range VBA

[pawcoyote]

Hi, I built the below to be able to clear data on various worksheets starting just below the main header line. What I am missing is how to be able to start the clear in a different cell/Column on each sheet. Meaning on one sheet the clear starts in B11 and on others it might start in C2. All the Headers start in Row 10 and all Data starts on row 11.

Global Code is below to show where the Headers are and where the Data starts

Option Explicit
Public Const rowHeaderInstall As Long = 10
Public Const rowDataStartInstall As Long = 11


Public headerInstall As Range


Public rowDataEnd Install As Long


Public wsInstall


Public rInstall As Range


The Clear Data info

Option Explicit
Sub Clear_Install
Set wsInstall = Worksheets("Install")
If msgBox("Are you sure you want to clear all the data on " & wsInstall.Name & "???", vbQuestion + vbYesNo + vbDefaultButton2, "Clear " & wsInstall.Name) = vbNo Then Exit Sub
With wsInstall
Range (.Rows(rowDataStartInstall), .Rows(.UsedRange.Rows.Count)).ClearContents
End With
End Sub

[Paul_Hossler]

C2 seems inconsistent


and on others it might start in C2. All the Headers start in Row 10 and all Data starts on row 11.


How do you know where to start on any given sheet?

A specific cell or some 'marker'?

[pawcoyote]

Right now it removes all data under the Headers starting in Row 11. I am trying to figure out how to get it to start in lets say B11.

[p45cal]

[COLOR=#141414][FONT="]and on others it might start in C2.
By C2 do you mean Column2, that is you want to keep columns A & B?

[pawcoyote]

Hi p45cal,

I think I can use the below which is simple. But, how can I make it adjustable beyond row 260 and not clear out any formulas if there are any. That is what I am working on how. I think I was trying to be too fancy before.


Sub Clearcells()Range("B11", "AD260").Clear
End Sub

[Dave]

Perhaps reviewing the etiquette of both forums would be helpful. Dave
Clear Data within Range | MrExcel Message Board (https://www.mrexcel.com/board/threads/clear-data-within-range.1202152/)

[pawcoyote]

Perhaps reviewing the etiquette of both forums would be helpful. Dave
Clear Data within Range | MrExcel Message Board (https://www.mrexcel.com/board/threads/clear-data-within-range.1202152/)

Not sure what you mean. But that isn't helpful.

[georgiboy]

Dave is explaining that you have cross posted, you are not supposed to cross post without providing the link to the other forum.

I apreciate that hedging your bets gets you the result you need faster by having the request in multiple locations, the problem is that the people on this and other forums help for free so it can be highly frustrating if you have the result you need from the other forum and people are still working on your request.

For this reason we ask you to post a link to the other forum so that we can see if you have your answer already and not waste any time.

This information is found in the rules - hence Dave's comment

[pawcoyote]

Thank you that was very helpful and will be sure to not cross post in the future. I wanted to delete that post but didn't see how to do that on the forum.


Dave is explaining that you have cross posted, you are not supposed to cross post without providing the link to the other forum.

I apreciate that hedging your bets gets you the result you need faster by having the request in multiple locations, the problem is that the people on this and other forums help for free so it can be highly frustrating if you have the result you need from the other forum and people are still working on your request.

For this reason we ask you to post a link to the other forum so that we can see if you have your answer already and not waste any time.

This information is found in the rules - hence Dave's comment

[georgiboy]

I think the bit below is rather confusing:

Meaning on one sheet the clear starts in B11 and on others it might start in C2. All the Headers start in Row 10 and all Data starts on row 11.

I think because you have stated that all headers start in row 10 but then also state that the clear might start in C2, if all headers start on row 10 and data in row 11 why would the clear start at C2?

If say on your sheets you have an area before the header that contains no data and this is the same for all the sheets you are running it on then you my be able to implement something like the below to get to the header:

Sub test()
Dim hRow As Long

hRow = Range("A1").End(xlDown).Row

MsgBox hRow
End Sub

Or if the header is the same on all sheets then maybe:

Sub test2()
Dim hRow As Long

hRow = Cells.Find("Header to find", , , xlWhole).Row

MsgBox hRow
End Sub

Thus making your code something like (untested):

Sub Clear_Install()
Dim hRow As Long
Set wsInstall = Worksheets("Install")

If MsgBox("Are you sure you want to clear all the data on " & wsInstall.Name & "???", vbQuestion + vbYesNo + vbDefaultButton2, "Clear " & wsInstall.Name) = vbNo Then Exit Sub
With wsInstall
hRow = .Find("Header to find", , , xlWhole).Row
Rows(hRow + 1 & ":" & Rows.Count).ClearContents
End With
End Sub

Hope this helps

[pawcoyote]

@georgiboy Thank you very much. That is a big help!

[snb]

If it is then:


Sub Clear_Install()
If MsgBox("Are you sure you want to clear all the data on " & wsInstall.Name & "???", vbQuestion + vbYesNo + vbDefaultButton2, "Clear " & wsInstall.Name) = vbNo Then Exit Sub

sheets("Install").Find("Header to find", , , 1).offset(1).resize(10^3).ClearContents
End Sub

[pawcoyote]

Hi snb,

I tried your code but I get an error when it runs. Goes to the sheets line and yellow's it all out.


If it is then:


Sub Clear_Install()
If MsgBox("Are you sure you want to clear all the data on " & wsInstall.Name & "???", vbQuestion + vbYesNo + vbDefaultButton2, "Clear " & wsInstall.Name) = vbNo Then Exit Sub

sheets("Install").Find("Header to find", , , 1).offset(1).resize(10^3).ClearContents
End Sub

[snb]

The macro should be in the file that contains a sheet named 'Install'.
Unless you post a sample workbook it's only guessing.

[pawcoyote]

It has to be on the exact worksheet and not in a Module? I built a Module for all my different codes. Thank you for the guidance.