Determine number of rows with data

[Simon]

Hi
I am using the macro below to pull some data from an external workbook.
The 2 issues I need to sort are:
1. The number of rows in the external workbook can vary. How do I amend this
code to pull all of the rows with data?
2. The number of rows in the autofill also may vary. How do I autofill only
the number of rows required? i.e the number of rows in column A that contain
data.

'Lookup Previous Month Sales
Columns("K:K").Select
Selection.NumberFormat = "General"
Range("K4").Select
Selection.FormulaArray = _
"=SUM(IF('C:\ABB\[SalesPreviousMonth.xls]Category by Customer -
Excel Ex'!R6C10:R263C10=RC1,'C:\ABB\[SalesPreviousMonth.xls]Category by
Customer - Excel Ex'!R6C14:R263C14,0))"
Selection.AutoFill Destination:=Range("K4:K300"), Type:=xlFillDefault
Range("K3").Select
ActiveCell.FormulaR1C1 = "Previous Month"

I am new to VBA so simple speak is appreciated.
Many thanks
Simon

 

[OssieMac]

Hi again Simon,

The following demonstrates the method of finding the last cell containing
data in a column. Explanation is that it is like placing the cursor on the
very last cell in a column and holding the Ctrl key and press the up arrow
and the cursor stops at the first cell with data.

You can then concatenate the variable in the range statement.

I have used Select in the example but there is generally no reason to select
cells to manipulate them. You simply address the range.

Dim lastRow As Long
With ActiveSheet
lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
End With

Range("A2:A" & lastRow).Select

 

[Simon]

Thanks very much OssieMac.
That solves the autofill on the current sheet.
How do I also include this in the external workbook reference to ensure I
get all the rows.

Note that this worksheet pulls data from 12 different external workbooks
(all with varying amount of rows)so I have 12 different sumifs.

Hi again Simon,

The following demonstrates the method of finding the last cell containing
data in a column. Explanation is that it is like placing the cursor on the
very last cell in a column and holding the Ctrl key and press the up arrow
and the cursor stops at the first cell with data.

You can then concatenate the variable in the range statement.

I have used Select in the example but there is generally no reason to select
cells to manipulate them. You simply address the range.

Dim lastRow As Long
With ActiveSheet
lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
End With

Range("A2:A" & lastRow).Select

--
Regards,

OssieMac

Hi
I am using the macro below to pull some data from an external workbook.
The 2 issues I need to sort are:
1. The number of rows in the external workbook can vary. How do I amend this
code to pull all of the rows with data?
2. The number of rows in the autofill also may vary. How do I autofill only
the number of rows required? i.e the number of rows in column A that contain
data.

'Lookup Previous Month Sales
Columns("K:K").Select
Selection.NumberFormat = "General"
Range("K4").Select
Selection.FormulaArray = _
"=SUM(IF('C:\ABB\[SalesPreviousMonth.xls]Category by Customer -
Excel Ex'!R6C10:R263C10=RC1,'C:\ABB\[SalesPreviousMonth.xls]Category by
Customer - Excel Ex'!R6C14:R263C14,0))"
Selection.AutoFill Destination:=Range("K4:K300"), Type:=xlFillDefault
Range("K3").Select
ActiveCell.FormulaR1C1 = "Previous Month"

I am new to VBA so simple speak is appreciated.
Many thanks
Simon

 

[OssieMac]

Hi Simon,

I need to see the code that you are using to get the data from the source
workbooks.

If you have not yet coded that part, then:

How do I identify the source workbooks? Are they all in the one folder?

Are the source workbooks the only workbooks in the source folder? If not then:

Do the source workbook names follow a pattern so that I can use wildcards to
identify and open each one in turn?

What is the Sheet name and column in the source workbooks where you want to
extract the data from.

What is the Sheet name and column in the destination workbook? (I am
assuming that you want the data in the destination workbook all in one column
one under the other. Is this correct?)

Where will the VBA code be located? (In the Destination workbook or in a
workbook of its own?) If in a workbook of its own, what is the name of the
destination workbook.

There may be more questions once I see what you return to me so continue to
monitor this thread as frequently as possible.

 

[Simon]

Hi
Code is below. All source spreadsheets sit in C:\ABB
The Destination workbook is C:\ABB\ABBPipelineReport.xls, Worksheet is
"Report".
You can see the name of each workbook in the code below i.e
SalesCurrentMonth, SalesPreviousMonth,SalesPreviousMonth-1,
SalesPreviousMonth-2 etc
The vba is in the Destination workbook.
Their are other source workbooks in C:\ABB
You will also notice that for some reason the 11th and 12th formulas for the
11th and 12th month of Sales open the external workbook. this is because only
these 2 formulas return #REF! if I dont. The previous 10 work fine without
opening external workbooks.

Hopes this all makes sense, thanks again for your help.
Simon


'Lookup Current Month Sales
Range("J4").Select
Selection.FormulaArray = _
"=SUM(IF('C:\ABB\[SalesCurrentMonth.xls]Category by Customer - Excel
Ex'!R6C10:R263C10=RC1,'C:\ABB\[SalesCurrentMonth.xls]Category by Customer -
Excel Ex'!R6C14:R263C14,0))"
Selection.AutoFill Destination:=Range("J4:J" & LR), Type:=xlFillDefault
Range("J3").Select
ActiveCell.FormulaR1C1 = "Current Month"


'Lookup Previous Month Sales
Range("K4").Select
Selection.FormulaArray = _
"=SUM(IF('C:\ABB\[SalesPreviousMonth.xls]Category by Customer -
Excel Ex'!R6C10:R263C10=RC1,'C:\ABB\[SalesPreviousMonth.xls]Category by
Customer - Excel Ex'!R6C14:R263C14,0))"
Selection.AutoFill Destination:=Range("K4:K" & LR), Type:=xlFillDefault
Range("K3").Select
ActiveCell.FormulaR1C1 = "Previous Month"

'Lookup Previous Month-1 Sales
Range("L4").Select
Selection.FormulaArray = _
"=SUM(IF('C:\ABB\[SalesPreviousMonth-1.xls]Category by Customer -
Excel Ex'!R6C10:R263C10=RC1,'C:\ABB\[SalesPreviousMonth-1.xls]Category by
Customer - Excel Ex'!R6C14:R263C14,0))"
Selection.AutoFill Destination:=Range("L4:L" & LR), Type:=xlFillDefault
Range("L3").Select
ActiveCell.FormulaR1C1 = "Previous Month-1"

'3 Month Rolling Average
Columns("I:I").Select
Selection.NumberFormat = "0"
Range("I4").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[1]:RC[3])"
Range("I4").Select
Selection.AutoFill Destination:=Range("I4:I" & LR), Type:=xlFillDefault

'Lookup Previous Month-2 Sales
Range("M4").Select
Selection.FormulaArray = _
"=SUM(IF('C:\ABB\[SalesPreviousMonth-2.xls]Category by Customer -
Excel Ex'!R6C10:R263C10=RC1,'C:\ABB\[SalesPreviousMonth-2.xls]Category by
Customer - Excel Ex'!R6C14:R263C14,0))"
Selection.AutoFill Destination:=Range("M4:M" & LR), Type:=xlFillDefault
Range("M3").Select
ActiveCell.FormulaR1C1 = "Previous Month-2"

'Lookup Previous Month-3 Sales
Range("N4").Select
Selection.FormulaArray = _
"=SUM(IF('C:\ABB\[SalesPreviousMonth-3.xls]Category by Customer -
Excel Ex'!R6C10:R263C10=RC1,'C:\ABB\[SalesPreviousMonth-3.xls]Category by
Customer - Excel Ex'!R6C14:R263C14,0))"
Selection.AutoFill Destination:=Range("N4:N" & LR), Type:=xlFillDefault
Range("N3").Select
ActiveCell.FormulaR1C1 = "Previous Month-3"

'Lookup Previous Month-4 Sales
Range("O4").Select
Selection.FormulaArray = _
"=SUM(IF('C:\ABB\[SalesPreviousMonth-4.xls]Category by Customer -
Excel Ex'!R6C10:R263C10=RC1,'C:\ABB\[SalesPreviousMonth-4.xls]Category by
Customer - Excel Ex'!R6C14:R263C14,0))"
Selection.AutoFill Destination:=Range("O4:O" & LR), Type:=xlFillDefault
Range("O3").Select
ActiveCell.FormulaR1C1 = "Previous Month-4"

'Lookup Previous Month-5 Sales
Range("P4").Select
Selection.FormulaArray = _
"=SUM(IF('C:\ABB\[SalesPreviousMonth-5.xls]Category by Customer -
Excel Ex'!R6C10:R263C10=RC1,'C:\ABB\[SalesPreviousMonth-5.xls]Category by
Customer - Excel Ex'!R6C14:R263C14,0))"
Selection.AutoFill Destination:=Range("P4" & LR), Type:=xlFillDefault
Range("P3").Select
ActiveCell.FormulaR1C1 = "Previous Month-5"

'Lookup Previous Month-6 Sales
Range("Q4").Select
Selection.FormulaArray = _
"=SUM(IF('C:\ABB\[SalesPreviousMonth-6.xls]Category by Customer -
Excel Ex'!R6C10:R263C10=RC1,'C:\ABB\[SalesPreviousMonth-6.xls]Category by
Customer - Excel Ex'!R6C14:R263C14,0))"
Selection.AutoFill Destination:=Range("Q4:Q" & LR), Type:=xlFillDefault
Range("Q3").Select
ActiveCell.FormulaR1C1 = "Previous Month-6"

'Lookup Previous Month-7 Sales
Range("R4").Select
Selection.FormulaArray = _
"=SUM(IF('C:\ABB\[SalesPreviousMonth-7.xls]Category by Customer -
Excel Ex'!R6C10:R263C10=RC1,'C:\ABB\[SalesPreviousMonth-7.xls]Category by
Customer - Excel Ex'!R6C14:R263C14,0))"
Selection.AutoFill Destination:=Range("R4:R" & LR), Type:=xlFillDefault
Range("R3").Select
ActiveCell.FormulaR1C1 = "Previous Month-7"

'Lookup Previous Month-8 Sales
Range("S4").Select
Selection.FormulaArray = _
"=SUM(IF('C:\ABB\[SalesPreviousMonth-8.xls]Category by Customer -
Excel Ex'!R6C10:R263C10=RC1,'C:\ABB\[SalesPreviousMonth-8.xls]Category by
Customer - Excel Ex'!R6C14:R263C14,0))"
Selection.AutoFill Destination:=Range("S4:S" & LR), Type:=xlFillDefault
Range("S3").Select
ActiveCell.FormulaR1C1 = "Previous Month-8"

'Lookup Previous Month-9 Sales
Range("T4").Select
Selection.FormulaArray = _
"=SUM(IF('C:\ABB\[SalesPreviousMonth-9.xls]Category by Customer -
Excel Ex'!R6C10:R263C10=RC1,'C:\ABB\[SalesPreviousMonth-9.xls]Category by
Customer - Excel Ex'!R6C14:R263C14,0))"
Selection.AutoFill Destination:=Range("T4:T" & LR), Type:=xlFillDefault
Range("T3").Select
ActiveCell.FormulaR1C1 = "Previous Month-9"
Application.ScreenUpdating = False
Workbooks.Open(Filename:="C:\ABB\SalesPreviousMonth-9.xls", Origin:= _
xlWindows).RunAutoMacros Which:=xlAutoOpen
ActiveWorkbook.Close False
Application.ScreenUpdating = True

'Lookup Previous Month-10 Sales
Range("U4").Select
Selection.FormulaArray = _
"=SUM(IF('C:\ABB\[SalesPreviousMonth-10.xls]Category by Customer -
Excel Ex'!R6C10:R263C10=RC1,'C:\ABB\[SalesPreviousMonth-10.xls]Category by
Customer - Excel Ex'!R6C14:R263C14,0))"
Selection.AutoFill Destination:=Range("U4:U" & LR), Type:=xlFillDefault
Range("U3").Select
ActiveCell.FormulaR1C1 = "Previous Month-10"
Application.ScreenUpdating = False
Workbooks.Open(Filename:="C:\ABB\SalesPreviousMonth-10.xls", Origin:= _
xlWindows).RunAutoMacros Which:=xlAutoOpen
ActiveWorkbook.Close False
Application.ScreenUpdating = True

 

[OssieMac]

Hi again Simon,

I think that the best way is to use named ranges rather than VBA variables.
Named ranges are as you would name a range on the worksheet. If you don't
know what they are then look up Defined Names in help.

These defined names get saved with the workbook. However, I don't think you
can do that without opening the workbook but I don't know any other way of
doing this so that you can use the defined names in the formula and I don't
know any other easy way of achieving what you want. If I were writing the
code I would do it entirely differently but I would need the workbooks to do
that.

Here is an example of just the first instance of finding the last cell in
the columns, defining a name and using the defined name in the formula. None
of it is tested.

If you copy your long line of code and paste it into you VBA editor then it
will probably mess up with breaks where it should not break and add double
quotes where it should not. Suggest you simply edit your code as per my
comments below.

I think I have got it right in that "Category by Customer - Excel Ex" is the
worksheet name.

'Lookup Current Month Sales
Range("J4").Select
With Workbooks("C:\ABB\SalesCurrentMonth.xls") _
.Sheets("Category by Customer - Excel Ex")

.Cells(.Rows.Count, 10) _
.End(xlUp).Name = "LastCol10"

.Cells(.Rows.Count, 10) _
.End(xlUp).Name = "LastCol14"

End With

'Now in your following line of code
'Edit R6C10:R263C10
'to R6C10:LastCol10

'Edit R6C14:R263C14
'to R6C14:lastCol14

Selection.FormulaArray = _
"=SUM(IF('C:\ABB\[SalesCurrentMonth.xls]Category by Customer - Excel
Ex '!R6C10:R263C10=RC1,'C:\ABB\[SalesCurrentMonth.xls]Category by Customer -
Excel Ex '!R6C14:R263C14,0))"

 

[OssieMac]

Hi again Simon,

I did a little testing and my answer is not correct. You would have to
prefix the named range with the entire workbook reference for the formula to
work. Therefore the following method of naming the entire range to which the
formula is applied is better because the workbook only needs to be referenced
once. However, as I said before, the workbook needs to be open. (See
alternative method at bottom of this post.)

With Workbooks("C:\ABB\SalesCurrentMonth.xls") _
.Sheets("Category by Customer - Excel Ex")

'Name the range in Col 10 to use in the formula
..Range(.Cells(6, 10), .Cells(.Rows.Count, 10)) _
.End(xlUp).Name = "Col_10"

'Name the range in Col 14 to use in the formula
.Range(.Cells(6, 14), .Cells(.Rows.Count, 14)) _
.End(xlUp).Name = "Col_14"

End With

Now in your code with the formula
Edit R6C10:R263C10
to Col_10

Edit R6C14:R263C14
to Col_14

What you could do instead of opening each workbook for this particular code
is to place code in the WorkbookClose event of each of the referenced
workbooks and update the named ranges each time the workbook is closed. The
following code goes in ThisWorkbook module of each of the workbooks.


Private Sub Workbook_BeforeClose(Cancel As Boolean)

'Edit sheet name if necessary
With .Sheets("Category by Customer - Excel Ex")
.Range(.Cells(6, 10), _
.Cells(.Rows.Count, 10) _
.End(xlUp)).Name = "Col_10"

.Range(.Cells(6, 14), _
.Cells(.Rows.Count, 14) _
.End(xlUp)).Name = "Col_14"
End With

End Sub

You will be prompted to save the workbook when closing because naming a
range is a change to the workbook and you want to save the defined name. If
you don't save then the named range is not updated. You could include code to
save the workbook automatically but it is dangerous if someone messes up with
editing the workbook and wants to close without the changes and start again
and automatic saving does not give them that option.