Free Microsoft Excel 2013 Quick Reference

Find calculated row with Worksheet_Calculate()??

Hi all,

I have a worksheet with about 500 rows, each one containing two columns with calculations in it (some simple formulas like H4 + J4).

Now here's what I need to do:
If one of the two calculations within one row throws a new result, I have to fire some Macros on the same worksheet. That's all.

If I tried to use "Worksheet_Change()" function it worked great, because I can determine the accessed row (which IMHO is not given within the "Worksheet_Calculate()" function). Unfortunately this function does only work for direct user input and not for output cells...

I really just would like to know how this could be done in VBA:

Sub Worksheet_Calculate()
Dim calculatedRow As Integer
calculatedRow = ????
Call myMacro(calculatedRow)
End Sub

Is there any suggestion or am I completely wrong here?

Thanks in advance, Paul


I currently have 3 worksheets (CB Design, INV Design, Summary) within a template workbook. I have a macro button on the CB Design sheet that performs one function. I also have a similar macro button on the INV Design sheet. The template will always open a file that will contain the following on the Summary sheet: cell A2 = CB1 and cell A3 = INV1. When the macro button on CB Design sheet is pressed, I would like to add the following:
after the macro performs what it already does on the CB Design sheet, go to Summary sheet, find last row that contains "CB", copy that row and insert it directly below itself.
When the macro button on INV Design sheet is pressed, I would like to add the following:
after the macro performs what it already does on INV Design sheet, go to Summary sheet, find last row that contains "INV", copy that row and insert it directly below itself.
I have searched for several hours on the excel forums with no luck. The main hurdle is, I am not looking for an exact value in the last cell (e.g. CB2), but rather just "CB" of "INV". Most of the solutions have been for specific cells or not to find last row with the partial value.

An example that lacks the copy function, but that I tried to modify and run as a test before incorporating it into the existing macros, but realized it is looking for an exact value is below:

Sub Test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "B").Value = "CB1" Then
Rows(i + 1).Insert
End If
Next i

End Sub
Sub Macro5()
Any help would be greatly appreciated.

Pat

i need a macro to Find last row with data and autofit, bold and center align all the cells in the row

I have two columns of dates.

I want to be able to find the row with the largest lead time.
I also want to be able to calculate the average lead time.

the lengths of the columns will vary as data is added and removed.

how would i do this in excel?

I'm sure this is simple, i've just had enough of trying and failing

many thanks

Is there a code that can find the last row with data on a worksheet. My worksheet has blank cells with formatting in them which causes the xlUp function to not work properly because it stops at the blank cells. I need a code that can find the last row with actual data in it. Any help would be great.

Like the title states I am looking for a means to find all rows that have a colored cell. My clients do not maintain any one color when setting a cell background. I am working in Excel 2007 and am aware of the sort columns by color and also have played with some VB to look at individual colors per column, but I was asked to find a means to sort all records to find ANY row with a cell that has a color.

Thanks

I have some VB code that selects the last row with data in an excel worksheet:

With Columns("A:I")
    LR = .Find("*", .Cells(1, 1), xlValues, xlPart, xlByRows, xlPrevious, False, False).Row
    End With
Now I want/need to tweak it so it only selects rows where the value in column "I" = 0. I'm pretty new to VB so I'm not sure the best way to do this. Any suggestions? Thanks!

I need to find the last row of worksheet A and use this as range for worksheet B. Workshhet A can change row count so I need B to canhange as well

Example:


	VB:
	
Range("A10").Select 
Selection.Copy 
Application.CutCopyMode = False 
ActiveCell.FormulaR1C1 = _ 
"=IF('Recap Report'!R[-3]C2="""","""",'Recap Report'!R[-3]C2)" 
Selection.Copy 
Range("A10:A56").Select  [COLOR="Red"]>[/COLOR] 
ActiveSheet.Paste 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any Suggestions?

Hi all,

I need to get a range VBA code to find the last row with a non-empty cell in a Multiple Column Range, specifically from columns A to AT

So far I have tried this one in different versions, but the best it does is give me the last row in Column "A", even when I use AT or any other column.

Range("A10", Range("a65536").End(xlUp)).Activate

I have tried alos some version wit R1C1 format, but no luck.

Help Please

The following line finds last "non-blank" row in a column.
I have a column that has formulas in 10000 rows.
Most of the time only first 7000 rows will have data, other cells below will have "". I need to find the last row with real data, not "".

Is there a way to add a condition or check to the following line?

thanks,
modytrane

Good day!

Problem;
I have two work sheets (A&B).
Worksheet A is used to enter data into the defined row of cells then produces a calculated output in a totals column.
Worksheet B is used to perform all the calculations for worksheet A's output. Calculations are set up on a row by row basis. For example, row 5 in worksheet A is directly dependent upon the formulas in row 5 of worksheet B.

So, the rows in worksheet A are directly aligned and connected to the calculation rows in worksheet B. All developed formulas in worksheet B must be kept hidden and not available to the user. Only the worksheet administrator has access to worksheet B's formulas. Therefore, worksheet B is password protected from all users.

I want to be able to insert "add" a row into worksheet A and have worksheet B's formula rows follow the change of inserting a row into worksheet A. This will allow the formulas in worksheet B to be directly connected on a row by row basis to worksheet A.

Right now if I add a row in worksheet A, I lose the formula connection in worksheet B and all calculated rows that reside underneath the newly inserted row in worksheet A are mis-calculated because the links are all messed up.

If needed, I can provide the Excel file.

Any ideas on this one??
Thank you for your help!
Jimbo73

I need to create a macro in Excel that will search every row of a sheet for 3 or more semicolons (";"). Each semicolon will be preceded and proceeded by text. If it finds a row with 3 or more semicolons listed it should delete that row. What I have so far is the following (works fine finding one semicolon, can't seem to figure out syntax for 3 or more):

Code:
Sub DeleteRowsContaining()
Dim rng As Range
Dim what As String
what = ";"
Do
Set rng = ActiveSheet.UsedRange.Find(what)
If rng Is Nothing Then
Exit Do
Else
Rows(rng.Row).Delete
End If
Loop
End Sub
Thank you!

Hi I am using Windows XP Excel 2003.
I am compiling a simple excel database that gets updated on a weekly bases, the volume of data is variable and not of fixed size. Whilst I have managed to get it fill by finding the next empty row, I am having trouble with deleting data over eight weeks of age. I have devised this code below to find the row with the date that is greater than eight weeks, and it deletes that row.

Sub SearchDate()

Dim gCell As Range
Set gCell = Columns(1).Find(DateValue(Range("a1")))
gCell.Select
Selection.Rows.Delete

End Sub

What I can’t seem to be able to get right is it to delete all the rows above it up to and including row three which will hold this older data.

Thank you for any assistance you can provide

Simonw

Is there such a way/function that i could simple hide/exclude any row with ZERO value in either a pivot table or in a regalur table of data? As of now, i have to manually find the row with zero value and hide them individually. It takes so much time. Please advise.

Thanks in advance for your help!

Hey All,

I have an Excel spreadsheet, in which resides a very complex set of polynomials we use to calculate engineering values from data we receive from our equipment. These calculations utilize many different cells, and also some coefficient data which resides in an array/table within another worksheet in the same book. The spreadsheets works like this: a technician enters the data from our equipment into one cell, and the output from the calculation is spawned in another, but what I am attempting to do is have a column of data converted. I cannot seem to find a way to enter the first cell of data into the calculation and have the calculation produce the results in another cell, but through the range of the entire column. Basically what I need is cell A1 on Sheet1 to be entered into Cell D1 on the calculation sheet, and have the calculation sheet return the results to cell B1 in Sheet1...............easy enough, but how do I make it so Cells A2, A3, A4, etc, are entered into Cell D1on the calculation sheet with the calculation returning their respective outputs into cells B2, B3, B4, etc...........

Another way around this, that I suppose could work, would be for me to move the calculations into sheet1, and copy the calculation so that each row has it's own, but I have to point the calculations to the correct coefficient value (which will be the same for all rows) and when I do this and try to copy/paste special, Excel sequentially changes the numbers; can I prevent this in any way? An example:

I have this formula in A6:
='COE' !D47+J5*('COE' !D48+J5*('COE' !D49+J5*('COE' !D50)))
When I try to copy/paste special to the next row, I get this:
='COE' !D48+J5*('COE' !D49+J5*('COE' !D50+J5*('COE' !D51)))
I need this:
='COE' !D47+J6*('COE' !D48+J6*('COE' !D49+J6*('COE' !D50)))

So, I need the function to get the data from the same cells on the COE sheet, but sequentially alter the row cell from J5 to J6, J7, J8, etc............

I know this is a mouthful, and there are probably more efficient means to achieving then end results, so I appreciate any input you all may have to offer.

ST

Hi,

I has a worksheet which gets data for a week range. My coloumns are fixed i.e one week range. But Rows can be differed and i need to calculate avg and based on avg i need to color the rows.

My format is like this :
A B C D E F G H
1
2
3
4
5
X

H is the coloumn where i take the avg. I have written VBA code by hard coding the row value, but in general it will vary. I am not sure how to find the last row and then calculate Avg.

I am adding the code which i did for constants Rows.

ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-7]:RC[-1])"
    Range("H2").Select
    Selection.AutoFill Destination:=Range("H2:H90")
    Range("H2:H90").Select
    Columns("H:H").Select
    Selection.NumberFormat = "0"
    
    Dim Rng As Range, cell As Range
Set Rng = Range("A2:G90")
  Rng.Interior.ColorIndex = xlNone
    
    For Each cell In Rng
        If cell.Value < Range("H" & cell.Row) * 0.2 Then
            cell.Interior.ColorIndex = 3
        ElseIf cell.Value > Range("H" & cell.Row) * 0.2 And cell.Value < Range("H" &
cell.Row) * 0.5 Then
            cell.Interior.ColorIndex = 6
        End If
Next cell

Could you please help me out in writing VBA code for getting last row and do the calculation instead of hardcoding the row range.

Thanks & Regards,
Pradeep

DXA.Calculator (Sample).xlsm

$10 (possibly more) for whoever can help me with this. As soon as possible would be wonderful. See attached spreadsheet for references.

I work in pediatric research at a hospital, and a few of the studies we are doing involve the patients getting a bone scan to measure density and mineral content. The scanning machine (DXA machine) allows us to extract the measurements (raw numbers, ex. 0.5457838), and paste them into Excel in the format seen in the MachineDataOutput worksheet. There are four different kinds of measurements I deal with (shown in D1, E1, F1, G1 of MachineDataOutput) Then, using the gender & age corresponding to the value, I take that measured value and type it into the "Measured" column cell of the corresponding table (based on gender and age) in the OldCalcSheet worksheet. Then, using the formulas embedded in the worksheet OldCalcSheet in the "Zscore" columns (formulas vary depending on which of the 4 types of data being calculated), a zscore is calculated- based on the mean and standard deviation which is specific to a gender and the age within that gender (see formula).

The problem I have is the tedious nature of the calculations- by hand, one at a time, when I have usually 200-300 rows of data to calculate. So why not create a macro that will do it for me?

This is what I need: A spreadsheet with a macro that will evaluate the data from MachineDataOutput and give me the zscore for each, in the next column over. Preferably with a click of a button that says "Calculate" or something. I have already done some code (probably all wrong) which stores all of the means and standard deviations for each age within a gender. It's all clearly labeled in the code. The "Whole Body Subtotal BMC" (OldCalcSheet) or "WB SUB_BMC" (MachineDataOutput) requires a third criteria for calculation, "CV(S)". Otherwise, the other three only need mean and standard deviation. Take a look at the spreadsheet attached, the formulas in the OldCalcSheet "zscores" columns, and my code I've started and let me know what you can do. I will consider paying more if it's done well. Thanks!

Hi,
Thanks in advance for taking the time to look at my question and giving it some thought. I am trying to simplify and reduce some manual data input for a voluminous workbook that my company uses. It is a huge set of spreadsheets with 100's of contracts listed by "policy number" with various columns of different pieces of data regarding the contract specifics. What is currently done manually is the summation of earned premiums on one worksheet sorted by policy is calculated giving the total earned premiums to date for that specific policy. This number is then copy and pasted to another spreadsheet on the same workbook in a row with the corresponding policy number to then be used formulaically with other numbers in the row. What I am wondering is if there is any way to instead of individually clicking and linking the corresponding cells manually if some sort of formula could be used to lookup the policy number from one spreadsheet and find it in another and put the earned premiums total into the correct cell and row. I am not very familiar with formulas typically used in excel but was wondering if some sort of self written vlookup formula would be appropriate. Any advice or solutions would be greatly appreciated by both me (getting major bonus points) and the guys manually inputting the data. The process usually takes them several hours and I am trying to find them a solution to simplify this process. Thanks again for any help!

i need to calculate number of rows with data in a particular column.
whether the number of rows filled (of column A) with data is 3 or more.
msg hello is printed.
is this syntax correct?

Code:
   

 If workbook2.Sheets("Sheet1").CountA(A) = 3 Then
  Msgbox "Hello"


I have a worksheet that we'll call "Main". I this worksheet, there are 12 columns.

Of those 12 columns, A thru L, I need to separate them out into separate based on 20 distinct values that can appear (multiple times or none at all for each value) in column B. (Note: If it would make it easier, I can re-arrange to make Column A the column with the criteria to sort these by. It would be a pain, but I can do it.)

Based on what is in Column B, I need the workbook to be sorted into the distinct subset workbooks, pulling only columns B, C, and G from the original. (The rest of the columns are used elsewhere in the workbook for calculations, so I need them to stay in the "Main" sheet, but I don't need them in the subset workbooks.
I further need it to not only sort these into individual workbooks based on column B, but also to only grab those rows that have a set value ("2" in my workbook) in column J...even though column J is not itself one that needs to be copied over...it's only a criteria determining what is copied.

Can this be done? I know I can do it with a combination of If statement and Vlookup, but if I do it that way, then any rows that don't qualify to be sorted into a particular workbook instead show up blank, so my data comes out with blank row, blank row, blank row, row with data (it qualifed with the if/vlookup), blank row, blank row, data row, etc.

My "Main" sheet usually has somewhere around 1,000 rows, and I have 20 different sheets to sort it into. I have to do this weekly, so I'm not inclined to sort/filter/copy/paste every time I need the report published.

Any help will be greatly appreciated.

I can't post an example because I don't have the necessary permissions to install anything (including the program to display worksheets) onto my computer. I can send you an example if you'd like, as I know that's an incredibly complicated question...and more complicated with no example.

Thanks!

Calinda

I am using Excel 97.
I use VBA to populate 2 cells with values.
When these cells are populated two adjacent cells containing formulas
trigger the worksheet calculate event. I do not want this to happen.

I have tried using application.caller..

Select Case TypeName(Application.Caller)
Case "Range"
v = Application.Caller.Address
Case "String"
v = Application.Caller
Case "Error"
v = "Error"
Case Else
v = "unknown"
End Select
MsgBox "caller = " & v

But it always returns "Error"

Is there another way to find out what is triggering the worksheet
calculate?

Hello. I need some help with batch calculations using macros in Excel. I have tried for a week to no avail. I have attached the spreadsheet with notes on which variables I need inserted where.

To summarize, I need to automate various cells to be placed in a separate calculations worksheet so every row gets a value and that value is inserted into the original worksheet. Take a look at my spreadsheet and it should make sense. I put comments in the highlighted cells (little red triangle) so just hover and you'll see which cells need to be moved where.

The ultimate goal is to get the 'Max Beam Radius (ft)' column filled automatically.

Please, any help would save me so much time, as I can not figure this baby out.

James

Find the last row with a value.

Say I have a worksheet with a data entery range of A1 to A100. The user is
required to enter data from row 1 downwards and cannot leave blanks between
rows.

I want to return the value in the last comleted row by function. Any ideas?
For example that last row with a value may be in cell A20 of the A1:A100
range.

Rgds,

Bruce

This is a rephrasing of another question posted earlier in another thread, that I have requested a moderator close/delete.

I'm trying to find the last row which has a value (non-empty) in column B. I want to then copy the formatting, validation and formulas of that last row (Columns A - BA) and paste it into the next available row.

I'm not very good / quite new to VBA, so here is what I've managed to piece together so far, from reading online (here and elsewhere). The comments show what I think will be happening and where... so you can see what I've got so far and what I'm still working on.

Any code / links / tips are appreciated.

Sub NewRow()
Option Explicit

'Declare variables;
Dim ws As Worksheet
Dim lastRowColB As Long
Dim screenUpdateState, statusBarState, calcState, _
    eventsState, displayPageBreakState As Boolean


'Get current state of various Excel settings;
screenUpdateState = Application.ScreenUpdating
statusBarState = Application.DisplayStatusBar
calcState = Application.Calculation
eventsState = Application.EnableEvents
displayPageBreakState = ActiveSheet.DisplayPageBreaks

'Turn off some Excel functionality;
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False

'Unprotect the worksheet, and unhide all rows so that the
'new row can be "inserted";
ActiveSheet.Unprotect
ws.Cells.EntireRow.Hidden = False

'Find the last row (lastR) which has a value in Column B;
lastRowColB = Range("B65536").End(xlUp).Row


'Copy the formatting, formulas and validation for the row
'found in the step above;


'Paste the formatting, formulas and validation to
'the next available row;

Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

'Rehide all unused rows (lastrow+1) and re-enable protection;

ActiveSheet.Protect

'Restore state;
Application.ScreenUpdating = screenUpdateState
Application.DisplayStatusBar = statusBarState
Application.Calculation = calcState
Application.EnableEvents = eventsState
ActiveSheet.DisplayPageBreaks = displayPageBreaksState

End Sub


I am producing a spreadsheet to track and analyze all sorts of data for a school. The problem is that when a child leaves the school I need to remove them (one child per row) from all calculations but still keep there data on file. I thought about adding a column with a drop down list stating "Yes" meaning the pupil had left but require some code to prevent this row from being involved in all calculations.

I'm not even sure this can be done but any help or ideas would be appreciated