1

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.

2
  • Have you tried to just put a formula for a single row, and double-click the auto fill button in the bottom right? (I don't think you need a macro to do this...). If you do want to use VBA however, you can loop through the data until it finds the first blank cell. Jun 29, 2011 at 12:58
  • @Breakthrough, I thought about looping through too and just found out, that you can ignore one blank cell (if a non blank cell is following) with ActiveSheet.UsedRange.Rows.Count But it depends on what point you want to stop counting.
    – Milde
    Jun 29, 2011 at 13:34

3 Answers 3

0

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
0

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
0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .