Formula to find last row in a range

[Shadkng]

Hi, I am using the formula below to determine if all entries in a column range are the same (if they are than it inserts that item). The range starts at G41 and the indirect is picking up the last row number based on a value in A1. I use this type of formula many times in my workbook and rather than have all of those indirects I am looking for an alternative - maybe using a method to find the last row with data. I researched online but not sure which is the best method. Any help is appreciated.

=IF(COUNTA($F$41:INDIRECT("$F"&$A$1))=COUNTBLANK($F$41:INDIRECT("$F"&$A$1))," ",IF(COUNTIF($F$41:INDIRECT("$F"&$A$1),$F$41)=(COUNTA($F$41:INDIRECT("$F"&$A$1))-COUNTBLANK($F$41:INDIRECT("$F"&$A$1))),$F$41,""))

 

[oldbrewer]

33	11					1
44						4
55		this is row 11			11			G is a helper column
		you want to find this						that provides the row number
		last row and put 11 in B1					of all non blank cells in column A
									B1 finds the largest number
									in the helper column
									it looks at 100 rows at present

<colgroup><col width="64" span="14" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Shadkng]

Hi, I don't understand this.

 

[Doflamingo]

Hi manually you can just click at the same time on Ctrl and down arrow. It will lead you to the last row with data and if you click twice, you will go at the last roww of the excel sheet, which is 1048576.

Regards

 

[oldbrewer]

shadkng the helper column looks at column A and if the cell is not blank returns the row number, so the biggest number in the helper column is the row containing the last non blank cell in column A.