Free Microsoft Excel 2013 Quick Reference

Store "Last Row" number in a variable

This is a quick one...

I have created a spreadsheet to load a variable amount of data into it when needed. I'm running Data Sorts, and need to be able to capture the last row in a variable, so i can reference that in the "RANGE function. So, it would be something like...

Dim lstrw as int
lstrw = Cells(Rows.Count, "F").End(xlUp)

Then use it as such...

Range ([a1], lstrw)

I'm sure this is easier than I'm making it to be... thanks guys!


Just a quick question guys:

Using a userform a user can first filter a worksheet by product and then search a column by 'keyword' with the results being displayed on a sepreate form.

The problem is that the range it searches is currently fixed, however I need this to vary depending on the amount of rows displayed after the original filter. The range to search is set as follows at the moment:

	VB:
	
 rngSearch = Range("a1:a50000").SpecialCells(xlCellTypeVisible) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
As you can see I have a total of 50000 rows which makes searching very slow. How could I change the Range("a1:a50000") bit so that it only searches the filtered results?? I know it's something to do with storing a last row number but i'm not sure after that.

I have search the site, but cant seem to find a way to load a var with the last row number in a range?

Any Ideas ??

thanks in advance

Hi
How can I get the last row number in a data as a result in another cell.
Eg: I have data of 10 rows (A1:C10) in Sheet1 and I need the number "10" to be displayed in Sheet2 A1. When I add more data in sheet1, the value of A1 in Sheet2 to be increased accordingly.
Thanks

I have Sheet with 40 employees who each proposes their work schedule, so I have to give each Employee access to the same sheet and want highlight and unlock only those cells that specific employee can use.

Each employee has to login from a drop-down (sourced from Sheet.Employee Master), so their unique Employee Number is in "A13" of Sheet.LOGIN

Can I identify the ROW number and then use that ROW number in a macro to highlight and unlock specific Range of Cells in Sheet.PROPOSED SCHEDULE?
---where "Sheet.LOGIN("A13") = (the value in the cell Col A:"row" of Sheet.PROPOSED SCHEDULE)

I have attached a scaled down version of the Workbook.

Following code is scaled down-- this is for Employee 02 who appears on ROW 16 of the sheet. (macro is same for each employee, just uses a different row)

Sub ProposedEmployee02()
'
' Proposed02 Macro
' Macro recorded 1/27/2009
' Highlights lit blue Range(COL B: COL F)  and
' For Range(cells for each workday in each workweek): Highlight cells lt yellow and unlocks for employee input
'
    Sheets("PROPOSED SCHEDULE").Select
    Range("B16:F16").Select
    With Selection.Interior
        .ColorIndex = 35
        .Pattern = xlSolid
    End With
    
    Range("G16:M16").Select
    With Selection.Interior
        .ColorIndex = 36
        .Pattern = xlSolid
    End With
    Selection.Locked = False
    Selection.FormulaHidden = False
    
    Range("O16:U16").Select
    With Selection.Interior
        .ColorIndex = 36
        .Pattern = xlSolid
    End With
    Selection.Locked = False
    Selection.FormulaHidden = False
    
   
    End Sub


How do I use a row number in a formula calculation in excel?
For .e.g I want to have cell = 3/(current row number).

Also is it possible to make row numbers start from 0?

Hi.

I need a macro that will find the last row containing data (i.e., the last cell in column A containing data) on the sheet called "RawData", then enter that particular row number in cell A1 on the sheet named "RD2."

Thanks for your help!

Glenn

I have several formulas in various places in a worksheet I use several times per day. They are all of the form (for example): =sum (E43:E71) (or other variant than a simple sum). What I'd like to do is store the starting row number (in this case '43') in some cell (say A98 which is named as 'StartRowNumber' for example), then change the formulas such that the '43' in '=sum (E43:E71)' refers to the StartRowNumber cell's content as the row reference. That way, when I (fairly frequently) have to change where these sums / stdevs, etc all change, I only have to change the startin row number in one place. TIA,

How do you store the col letter and the row number into a variable

Thanks,
GJ

Hi,

I am new to database connectivity, I am pulling data from MS Access Data base my query result is a single value which will be displayed in an textbox added to the worksheet using "Control ToolBox". query is working fine but i am unable to store the result in a variable need help on this

Thanks,

I have a sheet that I want to find the last row of data, Locate last row of data in column "C", subract previous row data column "C" from last row data column "C" and divide by previous row data in column "C". I also want to find last row data in column B and just display the value. I want to also be able to change the descriptions at the top depending the data being pulled. I have the following find last row code at the end but cannot figure out how to use my last row, previous row data in a formula.

Example Data

March 2008166.973.3%April 2008161.333.4%May 2008157.322.5%June 2008153.192.6%July 2008149.092.7%

Chart that I want the data to automatically update. The headings and Data will change depending on when the button is pressed to update data.
July 2008 LevelJuly/June Change (%)May/June Change (%)1-Year Change (%)Metropolitan AreaAtlantaBostonCharlotteChicagoClevelandDallasDenverDetroitLas VegasLos AngelesMiamiMinneapolisNew YorkPhoenixPortlandSan DiegoSan FranciscoSeattleTampaWashingtonComposite-10Composite-20

Last Row VBA

Private Sub CommandButton1_Click()
lastrow:
lastrow = ActiveWorkbook.Sheets("CaseShiller HomePrice_").UsedRange.Rows.Count
If ActiveWorkbook.Sheets("CaseShiller HomePrice_").Cells(lastrow, 1).Value = "" Then
ActiveWorkbook.Sheets("CaseShiller HomePrice_").Rows(lastrow).Delete
GoTo lastrow
End If
MsgBox lastrow
End Sub

Any ideas?

Would like to use a formula that returns the last row number from a column that has line items with information and blank cells.

Example:

---------Column A
row 1---text input
row 2---Text input results
row 3
row 4---numeric input
row 5---Numeric input results
row 6
row 7---End input

The answer in this case would be 7 because the last instance of information is in row 7.

Hope this makes sense.

Thanks

What I'm looking for is a macro that will replace the row number in a
formula with a new number that I designate through an input box.

Below is the code I have thus far. The problem lies in how I'm
defining the integer portion of the formula that I want to replace -
vbInteger (or vbLong) don't seem to work. Then finally I need to set
Section 2 in a loop through Column CN. Any advice would be greatly
appreciated!

---------------------------------------------------------------------------------------------------------------------------
Sub UpdateFormulas_2()

Dim LRowNumber As Long

LRowNumber = Application.InputBox _
(prompt:="Please enter the row number to update the formulas.",
_
Title:="Update Formulas", Type:=1)
If LRowNumber = False Then Exit Sub

Sheets("Review").Select

'Section 1 - Works just fine

ActiveCell.Select
If IsEmpty(Range("Input!A" & LRowNumber - 2).Value) Then
ActiveCell.Value = ""
Else
ActiveCell.Formula = "=CELL(""contents"",Input!A" & LRowNumber
- 2 & ")"
End If

'Section 2 - Needs help

ActiveCell.Offset(0, 1).Activate
ActiveCell.Select

ActiveCell.Formula = Replace(ActiveCell.Formula, vbInteger,
LRowNumber)

End If

Range("A1").Select

MsgBox ("The formulas were successfully updated to row " &
LRowNumber & ".")

End Sub
---------------------------------------------------------------------------------------------------------------------------

What I'm looking for is a macro that will replace the row number in a
formula with a new number that I designate through an input box.

Below is the code I have thus far. The problem lies in how I'm
defining the integer portion of the formula that I want to replace -
vbInteger (or vbLong) don't seem to work. Then finally I need to set
Section 2 in a loop through Column CN. Any advice would be greatly
appreciated!

---------------------------------------------------------------------------------------------------------------------------
Sub UpdateFormulas_2()

Dim LRowNumber As Long

LRowNumber = Application.InputBox _
(prompt:="Please enter the row number to update the formulas.",
_
Title:="Update Formulas", Type:=1)
If LRowNumber = False Then Exit Sub

Sheets("Review").Select

'Section 1 - Works just fine

ActiveCell.Select
If IsEmpty(Range("Input!A" & LRowNumber - 2).Value) Then
ActiveCell.Value = ""
Else
ActiveCell.Formula = "=CELL(""contents"",Input!A" & LRowNumber
- 2 & ")"
End If

'Section 2 - Needs help

ActiveCell.Offset(0, 1).Activate
ActiveCell.Select

ActiveCell.Formula = Replace(ActiveCell.Formula, vbInteger,
LRowNumber)

End If

Range("A1").Select

MsgBox ("The formulas were successfully updated to row " &
LRowNumber & ".")

End Sub
---------------------------------------------------------------------------------------------------------------------------

Hey all,

How can I get the filename of the spreadsheet and store that string in a
variable using vba? (just the filename, not the path)

Essentially, I'd like to be able to print out the filename of the
spreadsheet without having to hardcode the name.

Sort of like in bash, how you can refer to $0.

Thanks,

Mike

Hello,

I have the following array formula.

=SUM((MONTH('ADUNITS CSV'!$A$2:$A$29848)=MONTH($A15))*(YEAR('ADUNITS CSV'!$A$2:$A$29848)=YEAR($A15))*('ADUNITS CSV'!$G$2:$G$29848)*(ISNUMBER(SEARCH(B$1,'ADUNITS CSV'!$B$2:$B$29848))))

Instead of inputting 29848 (as in $A$29848) manually, I'd like it to be the result of the following formula.

MATCH("Totals",'ADUNITS CSV'!A:A,0)

Basically I need to tell Excel that the last row number in the range I need is the result of the MATCH formula.

I tried using the ADDRESS formula in combination with CONCATENATE as follows

="'ADUNITS CSV'!$A$2:" & ADDRESS(MATCH("Totals",'ADUNITS CSV'!A:A,0),1) which results in

'ADUNITS CSV'!$A$2:$A$29848 but Excel doesn't seem to interpret this result as a range, but as text so

=MONTH("'ADUNITS CSV'!$A$2:" & ADDRESS(MATCH("Totals",'ADUNITS CSV'!A:A,0),1)) results in an error.

Any ideas?

Thanks

Following a find command I want to store the row number to a variablke and
use that to move to a columb on that row

I have a range where some cells contain numbers and other (random) cells are empty. Can I calculate the sum of the last 3 numbers in that range with a single formula? What would such a formula have to look like? (Preferably, the formula will continue to work if I later expand the range and add new numbers and new gaps at the end.)

Thanks.

Hello All

I am writing code that looks at entered employee numbers, but what i need to do is make sure there are 5 numbers in the variable (Number) i.e 12160. i tried using the

	VB:
	
 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
But all i get is 2 because number is defined as an integer.

any idea's

thank you
Brian G.

I have over 2000 of these types of codes in a column. I want to keep just the last 2 numbers in a column next to it. Any ideas??

Mt-Clk16
Mt-Clk04
Bus-Cash01
Dy-Clk14
Perish-Clk-Mt05
Perish-Clk-Mt12
FSSeaf-Clk11
Mt-Clk11
Bky-Mgr14
Dy-Mgr12
FSLead04
Dy-Stud16
Bky-Clk10
Perish-Clk01

Hi, this is quite a random request, but I am wondering about the best syntax for using a VLOOKUP return as the row number in a CORREL function. I want to create rolling correlations from today's date. I have a VLOOKUP function that will return the row number corresponding to the chosen day's date.

I now need to use that returned value in the CORREL function. That is, I would like it to look something like:

=CORREL($E$VLOOKUP(today-90,AD5:AE3143, 2):$E$VLOOKUP(today,AD5:AE3143, 2),$E$VLOOKUP(today-90,AD5:AE3143, 2):$E$VLOOKUP(today,AD5:AE3143, 2))

Does this make sense? When I enter this, I am told that I have an error. Is there a better way to nest this vlookup?

Thanks so much for your help and please let me know if I can clarify anything!

Michael

Is there any way to show the row number in a cell?
I want to number each line in the worksheet
Gerald

How do I automatically insert row numbers in a column?

I want to create a custom updating formula that finds the last data element on a different sheet and then updates it's own formula. I have to do this because I want to find all of the blank cells and count them up (for missing data purposes). How can I go about putting the last row number in the formula?

This below is the best I can get but it 1004 errors out, I'm assuming because of my "Row" variable. Should I try a concatenate and custom build the formula each time within the macro?


	VB:
	
 MacroToRun() 
    Sheets("CleanData").Select 
    Range("A65536").End(xlUp).Select 
    Row = Selection.Row 
     
    Sheets("Chart Data").Select 
    Range("B2").FormulaR1C1 = "=COUNTIF(CleanData!R[-1]C[17]:R[Row]C[17],"""")" 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any Idea's?
Thanks!

My macro almost does everything I want except hide all the rows starting with the first T row in a particular column.

I am able to find that cell, but I then need to use the the cell number in my Rows select statement. Also when I try to use the variable LastRow, which works elsewhere in my code ( Selection.AutoFill Destination:=Range("E2:E" & LastRow), Type:=xlFillDefault) it does not work:

' Rows("26:LastRow & ").Select
' Rows("26:LastRow").Select
Rows("26:29").Select (Only this hardcode version works)

Ideally I want a variable for the 26 ( the first T row) and the 29.

Here is some of the code:

Sub provision_prepare()

stuff

LastRow = Range("A" & Rows.Count).End(xlUp).Row

stuff

Selection.AutoFill Destination:=Range("E2:E" & LastRow),

Selection.Find(What:="T", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=True).Activate

Somehow use the above selection to find the row number.

More stuff

The commented out statements my code returns with an error message, "Type Mismatch", even though in the little window I see 29 for LastRow (which is variant long but when I made it an integer I still got the same message),

' Rows("26:LastRow & ").Select
' Rows("26:LastRow").Select
Rows("26:29").Select

More Stuff

End Sub

I would be happy to read an article on this, but I cannot seem to find one on this specific subject. Probably I am not doing a meaningful google.

Is there anyone out there that can help me? Our programmer just resigned and they have not hired another yet. I mostly do sql querying for a living not "real" programming.

Thanks.