Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last blank row and use in a formula
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"))" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last blank row and use in a formula
Do you really mean the last blank row or the first blank row after the last
row of data?? -- Gary''s Student - gsnu200810 "Alex" wrote: 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"))" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last blank row and use in a formula
On Oct 28, 4:12*pm, Alex wrote:
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"))" 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last blank row and use in a formula
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 On 28 Okt., 22:12, Alex wrote: 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"))" |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find last blank row and use in a formula
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. "Gary''s Student" wrote: Do you really mean the last blank row or the first blank row after the last row of data?? -- Gary''s Student - gsnu200810 "Alex" wrote: 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"))" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average Formula to display blank cell if named range is blank | Excel Worksheet Functions | |||
Start Cell B1 then find first blank cell, insert subtotal, next non blank, then next blank, sutotal cells in between......... | Excel Programming | |||
Find First Non blank cell than find column header and return that value | Excel Worksheet Functions | |||
Formula to find equal values with blank cells | Excel Discussion (Misc queries) | |||
How Do I find/replace a blank cell with a formula? | Excel Worksheet Functions |