Find last blank row and use in a formula

  • Thread starter Thread starter Alex
  • Start date Start date
A

Alex

I have been trying to write a formula to a cell for hours and can't seem to
get it to work. I need to do the following:

Find the last blank row
set cell C2 with COUNTIF($b$2:$b$ lastblankrow,A2)
Copy the formula down from C2 until the last blank row (haven't gotten that
far yet)

Below is one of the many many ways I've tried this unsuccessfully. Can
someone help me? Thanks.

Set LastRowTemp = Cells(Rows.Count, 1).End(xlUp)(2).Row
Worksheets("DuplicateParts").Range("C2").Formula = "=CountIf(Range("B2:B
& LastRowTemp"),Range("A2"))"
 
Do you really mean the last blank row or the first blank row after the last
row of data??
 
I have been trying to write a formula to a cell for hours and can't seem to
get it to work.  I need to do the following:

Find the last blank row
set cell C2 with COUNTIF($b$2:$b$ lastblankrow,A2)
Copy the formula down from C2 until the last blank row (haven't gotten that
far yet)

Below is one of the many many ways I've tried this unsuccessfully.  Can
someone help me? Thanks.

Set LastRowTemp = Cells(Rows.Count, 1).End(xlUp)(2).Row
   Worksheets("DuplicateParts").Range("C2").Formula = "=CountIf(Range("B2:B
& LastRowTemp"),Range("A2"))"
Click to expand...

Try this site:

http://www.ozgrid.com/VBA/ExcelRanges.htm

It will show how to get the last cell containing data. But be
careful: If you clear data in the last cell and then try to get the
last cell again, it will point the empty cell. If you wanted to get
the last cell again, you'd have to save your workbook, close it, then
reopen it then try again. From what I can tell, there is no work-
around for this. It's just the way it is.
 
Hi

This should do it:

LastRowTemp = Cells(Rows.Count, 1).End(xlUp)(2).Row
MyFormula = "=CountIf(B2:B" & LastRowTemp & ",A2)"
Worksheets("DuplicateParts").Range("C2").Formula = MyFormula

Regards,
Per
 
The first blank row after the last row of data. What I've written below is
finding the first blank row in column A. I need to find the first blank row
after the last row of data in either column A or B, whichever has more data
in the row. Thanks.
 
Back
Top