Clearing data in multiple tabs, leaving header row

[akg742]

I'm putting together a spreadsheet template with multiple tabs (analysis, pivot table, data1, data2). Each time the data is updated, the contents of data1 and data2 need to be cleared, without deleting the header row (row 2). Since this is a template which will be used by my co-workers, I can't guarantee they're starting from a fresh file each time. And, if they're overwriting a file which already has data, I can't know if there's data in both data tabs. I've been trying to write if, then code but what I've come up with either does nothing or deletes the header.

A summary of what I need to do:

• check if there's data below row 2 in the data1 tab. If yes, clear everything from A2 to V last row of data. If no, do nothing in that tab. For yes or no, continue
• repeat the above for the data2 tab
• end macro

Thanks!

 

[Micron]

See if this deletes as you want for one sheet (change 004 to your sheet name) but test on a copy of your workbook. If it works, you can repeat but change the sheet name in the second part of your code.

Sub DeleteAllButHeader()
Dim Lrow As Long

Lrow = Sheets("004").Cells.Find(What:="*", After:=Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, _
            SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
If Lrow > 1 Then Sheets("004").Range("A2:A" & Lrow).EntireRow.Delete

End Sub

 

[akg742]

See if this deletes as you want for one sheet (change 004 to your sheet name) but test on a copy of your workbook. If it works, you can repeat but change the sheet name in the second part of your code.

Sub DeleteAllButHeader()
Dim Lrow As Long

Lrow = Sheets("004").Cells.Find(What:="*", After:=Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, _
            SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
If Lrow > 1 Then Sheets("004").Range("A[B][COLOR=rgb(184, 49, 47)]2[/COLOR][/B]:A" & Lrow).EntireRow.Delete

End Sub

As written above, everything below row 1 is deleted when there's data in the 004 tab. Changing the A2:A to A3:A (in red) leaves the headers in row 2 which is what I want. However, if I run the code with no data below the headers, it clears those out too and only leaves what's in row 1.

 

[Micron]

Not sure what you're saying (trying to format code tends to mess it up with html tags).

check if there's data below row 2 in the data1 tab

I think I missed that header row is not just row 1 - or is it? How many header rows are there?

if I run the code with no data below the headers, it clears those out too

Maybe that's the part I don't get. If there's no data below row 2 then what is it that gets deleted? You have formatting there?
You would have to change the IF Lrow > 1 to If Lrow > 2 I think.

 

[Micron]

OK after looking at the code instead of code that contains html I think your issue is that you need to change
Then Sheets("004").Range("A2:A" & Lrow).EntireRow.Delete
to
Then Sheets("004").Range("A3:A" & Lrow).EntireRow.Delete
so that the delete happens from row 3 on down? Also, I would change If Lrow as I stated above so that it doesn't run if your row count isn't greater than the row number that happens to be your last header row. If you added a 3rd row to the header, you'd change >2 to >3.

 

[akg742]

I think I missed that header row is not just row 1 - or is it? How many header rows are there?

Sorry about that. I have a really hard time summarizing everything people need to know. Row 1 has some instructions, Row 2 is the headers, and data would start in Row 3.

Maybe that's the part I don't get. If there's no data below row 2 then what is it that gets deleted? You have formatting there?
You would have to change the IF Lrow > 1 to If Lrow > 2 I think.

Part of my problem here is that I can't know if there's data in 004. Ideally, people using this spreadsheet will start fresh with the template (where the data tabs have nothing below row 2) and this part of the macro wouldn't be needed. But someone is going to use the file they saved yesterday for whatever reason, so I need to make sure everything below A2 is cleared. It's the "or" part that's tripping me up.

Then Sheets("004").Range("A3:A" & Lrow).EntireRow.Delete

I did figure that part out (I highlighted in your code quoted in my post but the formatting didn't show up )

 

[akg742]

OK after looking at the code instead of code that contains html I think your issue is that you need to change
Then Sheets("004").Range("A2:A" & Lrow).EntireRow.Delete
to
Then Sheets("004").Range("A3:A" & Lrow).EntireRow.Delete
so that the delete happens from row 3 on down? Also, I would change If Lrow as I stated above so that it doesn't run if your row count isn't greater than the row number that happens to be your last header row. If you added a 3rd row to the header, you'd change >2 to >3.

I wrote up all of the above and then tried your suggestion which was the wrong order because going to Lrow>2 fixed things.

Thank you so much!

 

[Micron]

Don't forget - 004 was my sheet name. You need to refer to your own sheet name(s). In summary, you don't need to worry about whether or not there is data below row 2. It will simply delete anything from the prescribed row on down IF there is. As mentioned, this includes any range formatting. Also, I think the After parameter in the find should be A2 because that value dictates what range to start after the range given. So if your last "non data" row is row 2, you should be looking after A2 by rights?

 

[akg742]

Don't forget - 004 was my sheet name. You need to refer to your own sheet name(s). In summary, you don't need to worry about whether or not there is data below row 2. It will simply delete anything from the prescribed row on down IF there is. As mentioned, this includes any range formatting. Also, I think the After parameter in the find should be A2 because that value dictates what range to start after the range given. So if your last "non data" row is row 2, you should be looking after A2 by rights?

Yep, I subbed in my tab names. It's working now but only if I keep After:=Range("A1"). When I changed it to A2, the data was still there after the macro was run.

Thank you very much for the help! I'm going to make some of my co-workers very happy once I finish the rest of my coding.