Excel VBA: .Range & Cells(row.count xlup

0
votes

I am trying to understand the following line:

.Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row).ClearContents

Can someone tell what .Range("A1:A" & means?

From my research so far I found the following:

  1. Cells refers to the cells
  2. .Rows.Count is a function that returns the number of rows in the table (=65536)
  3. , "A" refers to the column you want to search (here A)
  4. .End tells EXCEL where to start
  5. xlUp the direction to which EXCEL should search
  6. .Row the first cell from the bottom that is not empty
  7. .ClearContents deletes all values

Can someone tell if this is corrrect, and overall explain what the entire line is trying to do?

Assuming there may be values later added to column A, but I only wanted to delete the contents of 6 cells (A50:A55) how would I change this?

1
vbaexcel
If you want to delete those 6 cells, .Range("A50:A55").ClearContents will do it. The code you provide clears the used portion of column A. It can also be re-written much more efficiently. – Comintern

1 Answers

5
votes
.Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row).ClearContents

Breaking the line down (you had most of it right):

  1. The first thing to notice is the . at the start of many of the Properties/Methods. That tells us that the code must be within a With block, probably something like With Worksheets("Sheet1") or an equivalent. So everything starting with a . is just a shortcut to say Worksheets("Sheet1")..

  2. .Rows returns all the Rows in the worksheet, and .Rows.Count is therefore the count of how many rows there are. In older versions of Excel it was 65,536, and in newer versions it is 1,048,576.

  3. .Cells(.Rows.Count, "A") returns an object which refers to the last cell in column A, e.g. the Cell located at $A$65536

  4. .End(xlUp) says to go from that cell and find the previous non-empty cell in an upward direction. (If cell $A$65536 was non-empty, it would do something different, but I will gloss over that.)

  5. .Row then finds the row number of that non-empty cell. For the sake of this explanation, let us pretend that was cell $A$5201 so .Cells(.Rows.Count, "A").End(xlUp).Row will return 5201

  6. "A1:A" & 5201 will concatenate "A1:A" with "5201" (implicit type conversion from number to string) to generate the string "A1:A5201"

  7. Range("A1:A5201").ClearContents will clear the contents of the cells A1:A5201