How to get last row through VBA programming

Microsoft OFFICE

    Sponsored Links


  • 1. How do I extend or reduce the length of a diagonal line using VAB
    I have an XL spreedsheet On "sheet1" I have a basic drawing shape (a line) set at 45 degrees that I want to change in size with respect to a value held in "sheet1" range A1 Range A1 value is changed fequently as a result of changes on other sheets in this spreedsheet I would like this line to be automatically resized relative to the value of range A1 each time I visit "sheet1" I am trying to produce this effect using VBA - but so far without success. Can anyone provide me with the VBA code Your help would be very much appreciated Keith
  • 2. Additional controls
    Hi Everyone: In excel 2003, I was wondering if I create an add-in (.XLA) that has some additional controls, for example a treeview or listview, would these controls stay in the file or not. In other words, would my add-in work on someone else's computer if they do not have these controls? If not any suggestions? Thanks for your help. Bob
  • 3. Charts
    Hi Everyone: In excel, I would like to plot an array Y versus an array X as a scattered plot. When looking at the chart methods, I could only see how to plot a range, and not two arrays. For example I know to plot a range, I have to use: ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("C10:D17"). Can someone please show me how to plot Y vs. X without writing the data to the spreadsheet and then reading the range. Thanks; Bob
  • 4. Sending Printer command via VBA
    I have been searching for a way to send PJL commands to a Xerox 4595 from a Word document (VBA). What I want to accomplish is to set the printer output to 10 copies, duplex, 2 staples on the left side and 3 hole punched in the document. I starting looking at trying to pass the commands in a DOS Batch file.... Am I going in the wrong direction? I am a newbie when it comes to sending printer commands - could anyone offer advice?

How to get last row through VBA programming

Postby TXVnZGhh » Wed, 16 Sep 2009 14:34:01 GMT

Hi every one.

Usually I download a report in excel, apply filter and delete some rows. 
This changes my "last cell" reference, which has totals. Could you please 
tell me how do I get a row index/number. I want to use it it while writting a 


Re: How to get last row through VBA programming

Postby Scott Ketelaar » Mon, 21 Sep 2009 10:59:06 GMT

You can use this function

Function GetCellAddress(Cell As Range, Optional Absolute As Boolean = False)
'Function written 2009 by Scott Ketelaar
If Absolute = True Then
    GetCellAddress = Cell.Address
    GetCellAddress = Replace(Selection.Address, "$", "")
End If
End Function

like this.

GetCellAddress ("A1") produces A1
GetCellAddress("A1",true) produces "$A$1"
GetCellAddress(selection,false) produces the selection address

If you want only the row or column, you can use the .Row and . Column 

For Example:

Msgbox Range("A1").Row  Produces 1
Msgbox Range("A1").Column Produces 1 (Not a column letter, a column number)
Msgbox Selection.Row produces the selection row

Hope that helps


Re: How to get last row through VBA programming

Postby Scott Ketelaar » Mon, 21 Sep 2009 23:51:46 GMT

Oops, Just realized i made a slight mistake.
this line :
GetCellAddress = Replace(Selection.Address, "$", "")
should be this:
GetCellAddress = Replace(cell.Address, "$", "")


Similar Threads:

1.delete last row and return to the last row in the table


more experience needed...

if there a quick swift way to delete rows out the table and if it then
jumps out of the table due to being the last row, move the insertion
point to the previous row.

i have just thought i can run my row scanner routine backwards but i
would like to jump to the last row in the table

is there an easy way of doing that...

answer on a newsgroup

many thanks


remove the obvious capitals in my m@il address to send directly

2.Getting a row number of the last row?

I need to get the row number of my last row in a selection dynamically

How do I get this?


3.determining last active column (follow up to last active row)

Thanks to matt for helping me find the last active row 
(searching from the outside up) using this line of code:

Cells(Rows.Count, i).End(xlUp).Address

but when i tried to do the same with the columns using 
this line of code:

Cells(i, Columns.Count).End(xlLeft).Address

i get an app-defined or object defined error?  why is 
that?? what am i doing wrong??
thanks for the help, 

4.Excel VBA count rows from certain point and insert rows

I need vba code to count from a "found" cell (FIND "US03") in a column, 
searching from the top.  I want the macro then to count down from the found 
cell 170 rows and insert 2 rows. 


5.getting selected rows row by row


i have an excel sheet, where the user have to select one or more rows to 
I have to do something with it. But I can only do it row by row. So if 
the user select one row only, everything is fine. But if the user select 
more then one row, as I mentioned before, I have to do the work row by 
row. The question is, how can I see, if a row is selectet??? *grmpf*
ActiveWindow.RangeSelection.Address gives me all the selected cells. 
This doesn't help me much. I need somthing like this:

if [any special row].selected then
  ...[do my work with this special row]

But the attribut selected seems not to exist. How can I get the 
information, if a row ist selected or not?


H. Beese

6. Help me to first row matching First date and last row matching last row

7. Excel VBA Last Empty Row VBA paste Array elements

8. Find Last Row or Empty Row in VBA

Return to Microsoft OFFICE


Who is online

Users browsing this forum: No registered users and 48 guest