Cell Reference

[GreenyMcDuff]

Hi there,

I need to use a Cell Reference to select a range of data. I have read through some stuff on the net and found that the correct way to do this by following this format:

Range(Cells(1,1),Cells(40,1)).Select

Which should select from A1 to A40.

I am using the following code (Which may not be perfect ):

Sub Import()
Dim LastRow As Long, LastCol As Long
Dim Col_Dummy As Range
    Application.Workbooks.Open ("H:\VBA Macros\Barnwell, Robin\Sales_Core_Report_Update.xls")
 
        LastRow = Cells(Rows.Count, "A").End(xlUp).Row
        LastCol = Cells(20, Columns.Count).End(xlToLeft).Column
            ActiveSheet.Name = "Data"
 
                Array1 = Array("Branch Code", "Branch Name", "Class Code", "Class Type", "Sum of fund under management in EUR", "% AUM", "Fund Name", "CLIENT SUBTOTAL", "FUND SUBTOTAL", "%", "% TOTAL")
                Array2 = Array("B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L")
                    For a = 0 To 11
                        Set Col_Dummy = Range("A1:IV10").Find(Array1(a))
                            [COLOR=red]Range(Cells(Col_Dummy.Row, Col_Dummy.Column), Cells(LastRow, Col_Dummy.Column)).Select[/COLOR]
                                Selection.Copy
 
                                    Workbooks("Sales_Core_Report_Template.xls").Activate
                                        Range(Array2(a) & "6").PasteSpecial xlPasteAll
                    Next
End Sub

To do the same thing.

However, when the code runs, all the data in the sheet is selected.

When I debug the code and mouse over the variables highlighted in red I get the following numbers:
Col_Dummy.Row = 5
Col_Dummy.Column = 1
LastRow = 4257
Col_Dummy.Column = 1

Which in my mind says that A5 to A4257 should be selected.

Could someone point out where I have gone wrong. Thanks

Chris

 

[GreenyMcDuff]

It's OK,

I found the problem.

Someone had merged a cell at the bottom of the sheet