Vrftjkry

I have a repetitive process that I must do in Excel for test data collected. I have to do this process for every data point I collect. I collect over 200 data points each test.

My data is auto populated into Columns A and B. I need to be able to select all real data in column A, multiply it by a constant and place in a separate column. The problem is that my data in column A varies in the number of cells for each data point. For example, one set of test data will have 800 rows, then next will have 3200. It is always independent and I need to be able to select the entire row regardless of number of rows.

I need to be able to make this a Macro so I can do all of the other data manipulation necessry at once. The problem lies when I create the macro...I use shortcut to select all data and the Macro sees this step as selecting specified cells. So when I record the Macro for an 3200 cell length data point and then try to run it on a 800 cell data point, it selects 3200 cells. I need it to only select real data.

It sounds like you are trying to summarize a variable number of columns based on a field.
Try the Subtotal feature and see if that works for you. (see example)

If not, you may have luck using the Lookup & Reference functions to get a reference to where field A changes from 1 to 2, I would then attempt to generate a sum() with an array using the reference as input. Sorry I can't be more detailed, but I've never tried it before.

   A    B

  --------

+ 1    1.5

| 1    1.6

| 1    1.7

| 1    1.8

+ 1    1.9

1 Total  7

+ 2    1.5

| 2    1.6

| 2    1.7

| 2    1.8

+ 2    1.9

2 Total 7

Here's a macro solution.

I assumed your data is in column A with a header in cell A1, and you have a column header in column B, cell B1. Paste these 2 code blocks into a module and see if it works for you. The macro to run is MultiplyAllData().

This first block is a function to get the last row in column A

Function MyLastRow() As Long

    'This will give the last row in column A

    Dim theLastRow As Long



    Range("A1").Select

    Selection.End(xlDown).Select

    theLastRow = Selection.Row



     MyLastRow = theLastRow

End Function

This next block is the macro to run. It multiplies each value in column A by a constant (that you can edit), and puts the result in column B:

Sub MultiplyAllData()



     Dim ourLastRow As Long

     Dim myConstant As Double



     myConstant = 3.14           'you can edit your constant here



     ourLastRow = MyLastRow      'this is a call to our function



Range("B2").Select

'   NOTE:  below is the code I got when I first recorded my macro.      

'          I had test data down to cell A40.

'    ActiveCell.FormulaR1C1 = "=RC[-1]*3.14"

'    Selection.AutoFill Destination:=Range("B2:B40")

'    Range("B2:B40").Select

'

'   Here is the modified code from the macro, with more flexibility now

'   because of the variables.

ActiveCell.FormulaR1C1 = "=RC[-1]*" & myConstant

Selection.AutoFill Destination:=Range("B2:B" & ourLastRow)

Range("B2:B" & ourLastRow).Select



Range("B1").Select  'park the cursor

End Sub

Rudy:

I'd use something like this:

Range(Cells(1,ActiveCell.Column),Cells(WorksheetFunction.CountA(Columns(ActiveCell.Column)),ActiveCell.Column)).Select

This will select the number of rows containing data starting from row #1 within the active column.