ClearContents deleting data out of range

Question

I am trying to have this piece of code clear the contents of a certain range, before I bring new data. This range has a header right in the above row, which is getting cleared as well, although the range doesn't include it.

I've tried specifying the range and having other cells selected before running it, but the header gets cleared anyway.

With MyResults
    LastRowResults = Range("A16:K" & Rows.Count).End(xlUp).Row
    .Range("A$16:$K$" & LastRowResults).Select
    Selection.SpecialCells(xlCellTypeConstants, 23).Select
    Selection.ClearContents

It's clearing row 15 and even the content of cell A9, some of the times I run it.

The goal is to have this range cleared, keeping formulas in some columns intact, so that filtered data down the road can be brought in, making up the report required.

Vityata · Accepted Answer · 2019-09-09T15:25:34.677

1

Avoiding Select and Activate in VBA does miracles in performance and coding abilities (How to avoid using Select in Excel VBA). Concerning your code, try to avoid the header, if it is a default at line 16:

With MyResults
    LastRowResults = .Range("A17:K" & Rows.Count).End(xlUp).Row
    MsgBox LastRowResults 'Consider removing this line, once the code works.
    Dim deleteArea As Range
    Set deleteArea = .Range("A$17:$K$" & LastRowResults)
    deleteArea.ClearContents
End With

In the OP code, there was a lack of . before the Range in this line:

LastRowResults = .Range("A17:K" & Rows.Count).End(xlUp).Row

Thus, the lastRowResults was getting its value from the ActiveSheet or the Sheet in which the code resides (if not in a module). The . is quite important in the With MyResult.

In general, when the last row is searched for multiple columns, then one should define exactly the expected output. E.g., the for the biggest last row, something like this is way better:

LastRowResults = .Columns("A:K").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

edited Sep 09 '19 at 15:25

answered Sep 09 '19 at 12:23

Vityata

42,633
8
55
100

Does this prevent `.End(xlUp)` from making the range "negative" if the last filled cell in column A was, say, row 9? – Marcucciboy2 Sep 09 '19 at 12:24
1

I'm thinking that that's causing OPs frustration – Marcucciboy2 Sep 09 '19 at 12:27
@Marcucciboy2 - now, when I think about it, you probably have a point. – Vityata Sep 09 '19 at 12:27
1

@Marcucciboy2 - but I guess, the lack of `.` before the `Range` for the `lastRowResults` is the general reason - it takes the last row of another worksheet and thus it causes the "negative range". – Vityata Sep 09 '19 at 12:29
Hi @Vityata! Thanks for the prompt response. It keeps clearing from A9 downwards with the code below: With MyResults LastRowResults = .Range("A17:K" & Rows.Count).End(xlUp).Row 'MsBox LastRowResults 'Commented this, as it gave me an error, saying that there's a sub or function missing. Set deleteArea = .Range("A$17:$K$" & LastRowResults) deleteArea.ClearContents 'It deleted all formulas, but they need to be kept. End With Any idea as a workaround? Again: Thanks! – onit Sep 09 '19 at 13:09
@AntonioSantos - The MsgBox is simply a Message box. Try to make it and see the value of the `LastRowResults` - https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/msgbox-function – Vityata Sep 09 '19 at 13:14
The result is: 6, @Vityata! ...and every time I run it, this number decreases, deleting data upwards. – onit Sep 09 '19 at 13:19
@AntonioSantos - did you put the "DOT" - `.` before the word Range? Exactly as in my code? Like `.Range("A17:K")`? – Vityata Sep 09 '19 at 13:26
Yes, I did, @Vityata! – onit Sep 09 '19 at 13:29
@AntonioSantos - can you delete all the other worksheets, except for the `MyResults` and try again? – Vityata Sep 09 '19 at 13:29
@Vityata, I keep getting the same result, where it deletes everything and goes upwards, everytime I run it. – onit Sep 09 '19 at 13:37
@AntonioSantos - you deleted all the other sheets in the Excel file and now you have only one sheet left? – Vityata Sep 09 '19 at 13:48
Correct, @Vityata! – onit Sep 09 '19 at 14:03
@AntonioSantos - try this `LastRowResults = .Columns("A:K").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row` instead of `LastRowResults = .Range("A17:K" & Rows.Count).End(xlUp).Row` – Vityata Sep 09 '19 at 15:25
1

It still deletes rows' contents above - unfortunately. I'll try another approach later today and will let you how this unfolds. – onit Sep 09 '19 at 19:24
1

Hello @Vityata, Would that be the case to use .End(xlDown) instead? Anyway, I ended up setting a range whose contents are to be cleared and it suffices for now. Thanks a lot for getting involved! – onit Sep 11 '19 at 11:17

ClearContents deleting data out of range

1 Answers1