Once you have the filtered data, you can delete all these rows (while the remaining rows remain intact). Set rngLastCell = Sheet1.Range('A1').Offset( - 1, 0). One of the fastest ways to delete rows that contain a specific value or fulfill a given condition is to filter these. You don't have to first get the row number, and then build the cell reference. Note that really, if you want the cell reference, you should just use the following. So for instance, if you take your example data and also write something in cell FY4763, the above will still correctly return 9 (not 4763, which any method involving the UsedRange property would incorrectly return). This also works if you have data that go further down in other columns. Below is a list of the maximum number of rows, columns, cells, and individual sheets supported by various spreadsheet software. You may learn more about excel from the following articles. This searches up from the bottom of column A for the first non-empty cell, and gets its row number. How many sheets, rows, and columns can a spreadsheet have The number of cells, columns, rows, and sheets supported by a spreadsheet depend on the software used, and the data contained in the spreadsheet. Here we discuss the top 6 ways of counting rows in excel using the formula, rows with data, empty rows, rows with numerical values, rows with text values, and many other things that are related to counting rows in excel along with practical examples and downloadable excel template. Or the equivalent but shorter numofrows = Sheet1.Cells(,1).End(xlUp) numofrows = Sheet1.Range('A1').Offset( - 1, 0).End(xlUp).Row returns this number dependent on the version. The first has 65536 rows in a sheet, while the latter two have a million rows or so.

This will work, independent of Excel version (2003, 2007, 2010).

Excel how many rows available in excel