-2

I have searched this site and it seems like all the answers just point to finding the row number of the cell.

I am trying to set a range so that it will go from A1 to the end of the data in the A column. This spreadsheet will be updated weekly so there will be data added to it every week.

I was wondering what code would work so that I can either find the row number and somehow tie it in with my range code so that it will equal "A" + lastrownumber? OR if there is code that will provide the column and row number together? If I have missed the link to the correct answer a simple link will do as well and I apologize for the post and your time.

Here is my code: 

Sub NamedRange()

Dim Rng1 As Range
Dim newDate As Integer
Dim NumberOfRows As Range


Dim MyRange As Range
Dim lastRow2 As Range


lastRow2 = Range("A65536").End(xlUp).Row
'lastRow2 = LastRow

Set Rng1 = Sheets("Sheet1").Range(lastRow2)
ActiveWorkbook.Names.Add Name:="MyRange", RefersTo:=Rng1


Dim date1 As String
Dim dat As Date
Dim newPrice As Double


Set RgSales = Range("MyRange")
ARich
  • 3,230
  • 5
  • 30
  • 56
Adam Falkowski
  • 19
  • 1
  • 1
  • 4
  • 3
    Welcome to Stack Overflow. Why do you think the existing code samples are inapplicable? Finding the row number of the last used cell in a column sounds like exactly what you need. Ex: http://stackoverflow.com/q/6301665/190829 – JimmyPena Jul 12 '12 at 14:16
  • 1
    See this for the correct way of finding the row number http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba When you get the row number simply use it like `Debug.print Range("A" & rw).Address' – Siddharth Rout Jul 12 '12 at 14:24
  • This is an exact duplicate of [Last not empty cell in row; Excel VBA](http://stackoverflow.com/questions/4872512/last-not-empty-cell-in-row-excel-vba) and a gazillion other questions like it, even if the OP doesn't realize it. – Jean-François Corbett Jul 13 '12 at 07:30
  • Regarding all the `Range().End(xlUp).Row` answers, this is a great solution and I use it often, but beware that if your sheet is Autofiltered when you call the function, it will return the last row of filtered data, not the actual last row of all data. – sigil Jul 29 '13 at 21:39

4 Answers4

9

This will return the range object corresponding to the last filled in cell in column A

Range("A:A").Find("*",Range("A1"),SearchDirection:=xlprevious)

If you want the row number, use the following:

Range("A:A").Find("*",Range("A1"),SearchDirection:=xlprevious).row
mkingston
  • 2,678
  • 16
  • 26
  • +1 most robust answer. As the `end(xlup)` variants need testing for empty or full rows. http://stackoverflow.com/questions/4872512/last-not-empty-cell-in-row-excel-vba – brettdj Jul 13 '12 at 04:59
  • +1 for usnusual answer. @brettdj if the last cell has something in it then if it's excel 2010 then things will be running very slowly as I have problems when over 100k rows never mind >1mill rows! – whytheq Jul 13 '12 at 06:44
2

This will give the last row in a given column

= Cells(Activesheet.Rows.Count, ColumnNumber).End(xlUp).Row (Fixed per @Gimp)

you then have a reference you can use to add to the data - e.g if you want to look in column "A", then that would be columnnumber 1. feed that into the function, then you can use Cells(NumberReturnedFromFunction,ColumnNumber) to address that cell, and add .Address if you want the A1 style of address

SeanC
  • 15,695
  • 5
  • 45
  • 66
  • 1
    You should replace the hard coded max row limits of 65536 / 1048576 with a dynamic reference so it'll be compatiable both ways by replacing the number with Activesheet.Rows.Count. (see my answer) – danielpiestrak Jul 12 '12 at 14:55
0

Try using something like this:

Activesheet.Cells(Activesheet.Rows.Count, "A").End(xlUp).Row

You can replace Activesheet with references to a sheet's index # like Sheets(1) or the sheet name like Sheets("Sheet1")

By using the Rows.Count it will check to see what the max rows are and be compatible across all versions of Excel.

In the end you can use this within your range reference like this:

Msgbox Sheets(1).Range("A" & Sheets(1).Cells(Sheets(1).Rows.Count, "A").End(xlUp).row).value

But I'd probably rewrite that as

With Sheets(1)
    Msgbox .Range("A" & .Cells(.Rows.Count, "A").End(xlUp).row).value
End With
danielpiestrak
  • 5,279
  • 3
  • 30
  • 29
  • `Activesheet.Cells(Activesheet.Rows.Count, "A").End(xlUp).Row` will return the incorrect row if the last cell (e.g. A65536) has data. It will skip to the next break in the data, or to row 1 if the entire column contains data. – Chris Kent Jul 12 '12 at 15:50
  • @Phydaux What would you recommend? – danielpiestrak Jul 12 '12 at 16:04
  • In the rare case that the last row has data, you can check before going xlUp. e.g. If len(range("A" & activesheet.rows.count))> 0 then... I've never run into that, but it could happen. – Jon Crowell Jul 13 '12 at 02:16
0

In case there are gaps in the data I'd avoid using xlDown so something like the following is fine. Try it in the immediate window:

Activesheet.range("A1:A" & Activesheet.Cells(Excel.Rows.Count, 1).End(Excel.xlUp).Row).select
whytheq
  • 34,466
  • 65
  • 172
  • 267