Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

How to determine the last row in a given worksheet

Hello.

I have a macro that formats the worksheet from a text file to an excel file.
I insert a column and create a formula on the second row of that column.
How do I determine the last row in the worksheet so I will paste my
formula until the last row on the new column?

Thanks in advance.


Post your answer or comment

comments powered by Disqus
I am trying to find out how to find the last value in a row. Here is how my spreadsheet is set up: I have dates across the top row and each row under it has returns...

A1 = Name, B1 = Latest Month, C1 = Jan 07, D1 = Feb 07, E1 = Mar 07
A2 = Mike, B2 = ?, C2 = 1%
A3 = Steve, B3 = ?, C3 = 2%, D3 = 5%
A4 = John, B4 = ?, C4 = 0%, D4 = 8%, E4 = 10%

I want column B to return Jan 07 for Mike, Feb 07 for Steve, and Mar 07 for John. Is there a function that can do this? Thanks for any help you can provide.

Is there a way to add a row of data to a worksheet using a macro? I need to
determine the last row of data already there and then append on the next row
some static data. The rows of data will change each time I use this workbook.

Example:
On a cloumn with 1224 rows of data, I need to add on the 1225 row the number
1000 automatically. The rows of data will change each time I use this
workbook.

Thank you

Ric

How can identify the last value in a column cell. example: in column A:A has A1 =LV-001, A2=LV-002, A3= LV-004 . The last value is LV-004

Hi,

I have a workbook with multiple sheets that need to be combined into one. There's a referral date in column A, and first and last name in columns C and D, respectively. I have a macro that combines the multiple sheets that works great, or it would, if everyone filled out the date that goes in column A. But they don't. They do, however, fill out every name in Column D. How can I get the macro to find the last row in Column D, but copy the data in starting at column A, even if a cell in column A is blank? My complete code for the loop is below, and this in particular is the line I need to change:

Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)

For J = 2 To 3
Sheets(J).Activate
Range("A3:S1000").Select
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
Selection.Rows(i).EntireRow.Delete
End If
Next i
Selection.Offset(0, 0).Resize(Selection.Rows.Count - 2).Select
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next

Thanks in advance,
Jocelyn

Hi,

I have a task that pulls out information from the website. I need to write a vba code to find the last row. Since the no of rows are always not the same. I would need the excel masters help to solve it. Just a small piece of guidance would also help me.

In my work sheet I need to find the word " Function Name: Cleaning" if this is there then I should find the occurence of the word " name". If I find that then the code should copy the values in the column after name till it sees a space ( means no value). Should copy till that and paste that in another work sheet.

Am confused to consturct the if cases here. Have attached a sample file.

Any help is greatly appreciated.

Thanks in advance for the help.

I want to find the last row in within the range Z22 to AB52. However an error occur if all the cells within the range are empty. I tried to add in "On Error Resume Next" but the lastrow value did not return a 0.

How can i assign lastrow value to be 0 if all the cells are empty?
Thanks for any help in advance!

Sheets("sale").Select
With Range("Z22:AB52")
On Error Resume Next
lastrow = .Find(What:="?*", After:=.Cells(1, 1), LookIn:=xlValues, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

End With

A co-worker asked me how to programmatically select the first cell of the
last row in a range in Excel 2003 (Windows XP). We found the Help reference
to selecting the last cell in a range, but we couldn't come up with a good
solution for the first cell in the last row, other than a "SendKeys" type of
solution to send "Ctl-down arrow", which is a bit too imprecise.

Neither of us is a strong coder, but he's trying to use a macro to get
there.

Any ideas would be welcome!

--
Susan Ramlet
**please reply to the newsgroup so others may benefit**

I know there is a way to find the last used row in a spreadsheet, but I'd like to know if there is any way to determine the last row used in a range in a spreadsheet in a closed workbook.

My cells in a column are filled with amounts for
which I have to change the last digit into a letter.
E.g. if last digit is 1 and the amount is positive it should be changed into
an A if it's negative it should be changed in a J
If the last digit is 2 and the amount is positive it should be changed into a
B, if it's negative it should be changed into a K.
If the last digit is 3 and the amount is positive it should be changed into a
C, it it's negative it should be changed into a L.
And so on.

I tried to use this formula but I'm hitting the 7 nested functions ceiling
because I need to change the last digits 0-9 both positive and negative.
Example: =IF(J4>0,REPLACE(J4,LEN(J4),1,IF(RIGHT(J4,1)="1"," A",IF(RIGHT(J4,1)
="2","B",IF(RIGHT(J4,1)="3","C",IF(RIGHT(J4,1)="4" ,"E"))))))

Is there any other way you can think of?
Thanks.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200510/1

My cells in a column are filled with amounts for
which I have to change the last digit into a letter.
E.g. if last digit is 1 and the amount is positive it should be changed into
an A if it's negative it should be changed in a J
If the last digit is 2 and the amount is positive it should be changed into a
B, if it's negative it should be changed into a K.
If the last digit is 3 and the amount is positive it should be changed into a
C, it it's negative it should be changed into a L.
And so on.

I tried to use this formula but I'm hitting the 7 nested functions ceiling
because I need to change the last digits 0-9 both positive and negative.
Example: =IF(J4>0,REPLACE(J4,LEN(J4),1,IF(RIGHT(J4,1)="1","A",IF(RIGHT(J4,1)
="2","B",IF(RIGHT(J4,1)="3","C",IF(RIGHT(J4,1)="4","E"))))))

Is there any other way you can think of?
Thanks.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200510/1

Hello experts:

How to find the largest number in a column and show it in a specific cell ?

Thanks

Ivan

Dear All,

I would like to use the frequency function in a code. I have the worksheet having the data in two ranges for the data_arrays and bins_array.

Like:

Dim datarange As Range
Dim binrange As Range

Set datarange = ActiveSheet.Range("A2:A10")
Set binrange = ActiveSheet.Range("E2:E4")

And I would like the code to add the frequency values in a given range e.g. (F2:F5).

How could I code this?

Many thanks in advance!

JT

Hi ,
How to use the Prod Function in a list of averages to find the lease common
value !
here is an example.

AVRG
26.27
-------------------
29.27
25.61
26.17
25
25.32
--------------
3 | These are a list of Over and Under values of the above
-0.66
-0.1
-1.27
-0.95
--------------
3 | These are a list of compile Positive and Negitive values
also average

| trying to locate which one is within range of the
average,one group
-0.745
| is always known ,in this case it's (25) of the above
values,which now leave (4) items to be measure by an average, maybe if I
could find the Standard Error in the Averaging,it would help in locating(plus
or munis) and the amount !

Thanks in advance

Joseph..

hi,

I have this code which i have used before to determine the last row in a column and for some reason if the worksheet is blank and i use this approach to determine the last row on a blank worksheet it gives me a run time error 91 which is something about an "Object variable or With block not set".

here is the code i use to identify the last row......note: i am also enclosing the file which produces the runtime error

Set ws =
Workbooks(fl_name).Worksheets(1)


last_row_sheet1 = ws.Columns(1).Find("*", ws.Cells(1, 1), _
                    xlValues, xlPart, xlByColumns, xlPrevious, False, False).Row


any ideas?

If you aren't interested in context, but think you can help me, please skip to the boldized section below for the abridged version of the problem!

I have a worksheet with a button that updates a second sheet with a current list of what we have in stock (so we can print out the second sheet and use it to manually take inventory). So, it copies a range named "Inventory," on the first sheet, that has a row count that fluctuates with the number of different commodities we actually have in stock at that given moment and then pastes that copied selection into the second sheet, over top of whatever old inventory was left over from the last time we pushed the update button.

What I'm trying to do is add a signature box (for inventory-taking accountability) directly below the pasted area after I paste it. Now, of course, the copied and pasted area could have 5 rows or it could have 15, and when I copy it from the first sheet and paste it into the second it doesn't preserve the name of the range on the second page, so what I really need to do to add the signature box directly below the last row in the pasted area is specify the location in relation to the last row in the pasted selection, but I don't know how to do that. Using ActiveCell always refers to the top-left-most cell in the selected area. So...

If I have an area selected, how do I refer to the bottom-right-most cell in that selection (in the same way that ActiveCell refers to the top-left-most cell)? Or at least the bottom row? Or, even better, the row below the bottom row?

ps. I can't upload an example sheet due to a nondisclosure agreement I've signed.

hi, im on xl 2003, i have a box with 6 rows where numbers can be added in, but in another cell, i need to know the last number that has been added in this list, ie

......a
1 678
2 456
3 143
4
5
6
7
8_____

so i need cell a8 to keep updating with the last number that has been placed in the range a1:a6, thanks

Hi friends!

I have this formula in C7: Code:
The Table Array contains a list of 12 months, and there will be a match to the Lookup Value in A7 each month.  I would like
C7 to reflect the last completed match in the list (it will be updated each month).  So, when June is the last completed
month, C7 will return the matching amount from the Table Array for June; then when July is completed, I would like C7 to
reflect the matching amount for July.  

Can anyone help me accomplish this? Thank you for your help!

Hello,

I'm at my wit's end trying to figure this one out. I'm working on a macro that opens Excel files and finds the last row. The purpose is to find files that were saved with unused rows causing wasted space. I've been using

ActiveCell.SpecialCells(xlLastCell).Row

to find the last row, which has been working, but it breaks when it reaches a protected sheet. Obviously I can unprotect the sheet in VBA, but the problem comes when it hits a protected sheet that has a password. It will then ask the user for the password (which is unknown), and when they hit cancel, the macro breaks.

So what I'm really looking for is one of three things:

1) Is there a way to find the last cell in a worksheet that works with protected cells?

Please don't suggest
Cells.Find("*", After:=.Cells(1), _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row

or similar, as I've already tried it and it's not finding the true last cell. I believe the cells I'm looking for do not contain data or searchable formats.

2) Is there a way to find out if a worksheet is password protected?

I know there are ways to find if a worksheet is protected in general, but is there a way to see if there's a password associated with it? Note: I'm not looking for workbook protection, but worksheet protection. If this is the case, I can skip that specific sheet and move on.

3) Is there a way to get around the prompt for a password and skip the sheet/file?

I'd like to run this macro overnight (it searches a large number of files) and would like it to be able to skip over this problem without user input. So instead of fixing the break problem when some one hits Cancel, I would like it to skip the password prompt all together.

If any one knows how to do one of those three things, or has any other suggestions, I would greatly appreciate it.

Thank you in advance.

Greetings,

Is there a better way to determine the last row in my Sumproduct formula?

I use the following formula to count - BUT, my last row may not always be 112 - can it be dynamically figured out via the formula??
=SUMPRODUCT(--($B$22:$B$112=$C5),--($C$22:$C$112=$D5),--($F$22:$F$112=E$3))

Thanks!

Hi,

I am not sure how to find the last column in a row using excel macro programming.

What I need to do is, to find the last column of row 1,

For example, I am using row 1 as my cursor, i start from column "G", and i don't know where the last column in row 1 is...

I have used lastrow but not sure how to use lastcolumn
the codes i used for last row is

Sheet1LastRow = ThisWorkbook.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

A while ago I modified a code I found on the web to search for the last row of data then enter in new data based on entry within a form onto the next row. I now need to modify this code to be based upon a specific Row (B to be exact). Can someone please verify this code:

Dim iRow as Long
Dim ws as Worksheet

iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

I then modified the last line to the following in an attempt to specify the search for the last row in Column B

Range("B65535").End(xlUp).Offset(1, 0).Select

But I then get a compile error....

any Ideas what I did wrong...
BV

Does anyone have any idea on how to determine the values under following
conditions?
Given a list of numbers sorted by ascending order from top to bottom under
column A, I would like to determine the minimum difference on the range
within this list covering 50% of total numbers, and return the smallest
number within the range in cell B1 and the largest number within the range in
cell B2.
Does anyone have any suggestions?
Thank you in advance
Eric

I'd like to find the first sheet in my workbook.

I know how to find the last sheet:

    Sheets.Add
After:=Sheets(Sheets.Count)
    sheetname2 = Sheets(Sheets.Count).Name
I would like to find the first sheet in a similar manner.

Anybody?

The following code selects all cells from Q12 through the last data row of database.

Let's assume the last row of data is row 300. After the cells of Q12 thru Q300 are selected, I want the active cell to be 1 cell below the last data row. The previous selection is then no longer needed.

My request is for a diffeent need, than what this code is used for else where..

Sub SelectAlleMails()

  Dim Rng As Range
  Dim RngEnd As Range
  Dim Wks As Worksheet
    Set Wks = Worksheets("email list")
    Set Rng = Wks.Range("Q12") 
    Set RngEnd = Wks.Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False)
    If Not RngEnd Is Nothing Then
       Set Rng = Rng.Resize(RngEnd.Row - Rng.Row + 1)
       Rng.Select
    Else
       MsgBox "Range is Empty."
    End If
    Selection.Copy
End Sub
Thanks for the help
Matt


No luck finding an answer? You could always try Google.