Find Next Blank Cell in Range

[redbaron06]

Hi all,

I am working with the code below that opens closed workbooks to extract data from them. I know that it is inefficent code. The ranges defined differ by 501 cells (1 for the title line, and 500 for the data). However, the number of rows of data (A:K) for each list is different.

I was wondering if anyone knew a way to either make the code more efficent, perhaps using For Each C? Or perhaps, and ideally, have the code write to the next blank cell in the range A46:K37000 each time it tranfers the data between workbooks. Not asking for you all to write the code, just guide me on the right track.

Thanks in Advance for any help!

Sub DoGetData()
Dim strFile As String
Dim strRange As String
Dim strFile2 As String
Dim strRange2 As String
Dim strFile3 As String
Dim strRange3 As String
Dim strFile4 As String
Dim strRange4 As String
Dim strFile5 As String
Dim strRange5 As String
Dim strFile6 As String
Dim strRange6 As String
Dim strFile7 As String
Dim strRange7 As String
Dim strFile8 As String
Dim strRange8 As String
Dim strFile9 As String
Dim strRange9 As String
Dim strFile10 As String
Dim strRange10 As String
Dim strFile11 As String
Dim strRange11 As String
Dim strFile12 As String
Dim strRange12 As String
Dim strFile13 As String
Dim strRange13 As String
Dim strFile14 As String
Dim strRange14 As String
Dim strFile15 As String
Dim strRange15 As String
Dim strFile16 As String
Dim strRange16 As String
Dim strFile17 As String
Dim strRange17 As String
Dim strFile18 As String
Dim strRange18 As String
Dim strFile19 As String
Dim strRange19 As String
Dim strFile20 As String
Dim strRange20 As String
Dim strFile21 As String
Dim strRange21 As String
Dim strFile22 As String
Dim strRange22 As String
Dim strFile23 As String
Dim strRange23 As String
Dim strFile24 As String
Dim strRange24 As String
Dim strFile25 As String
Dim strRange25 As String
Dim strFile26 As String
Dim strRange26 As String
Dim strFile27 As String
Dim strRange27 As String
Dim strFile28 As String
Dim strRange28 As String
Dim strFile29 As String
Dim strRange29 As String
Dim strFile30 As String
Dim strRange30 As String
Dim strFile31 As String
Dim strRange31 As String
Dim strFile32 As String
Dim strRange32 As String
Dim strFile33 As String
Dim strRange33 As String
Dim strFile34 As String
Dim strRange34 As String
Dim strFile35 As String
Dim strRange35 As String
Dim strFile36 As String
Dim strRange36 As String
Dim strFile37 As String
Dim strRange37 As String
Dim strFile38 As String
Dim strRange38 As String
Dim strFile39 As String
Dim strRange39 As String
Dim strFile40 As String
Dim strRange40 As String
Dim strFile41 As String
Dim strRange41 As String
Dim strFile42 As String
Dim strRange42 As String
Dim strFile43 As String
Dim strRange43 As String
Dim strFile44 As String
Dim strRange44 As String
Dim strFile45 As String
Dim strRange45 As String
Dim strFile46 As String
Dim strRange46 As String
Dim strFile47 As String
Dim strRange47 As String
Dim strFile48 As String
Dim strRange48 As String
Dim strFile49 As String
Dim strRange49 As String
Dim strFile50 As String
Dim strRange50 As String
Dim strFile51 As String
Dim strRange51 As String
Dim strFile52 As String
Dim strRange52 As String
Dim strFile53 As String
Dim strRange53 As String
Dim strFile54 As String
Dim strRange54 As String
Dim strFile55 As String
Dim strRange55 As String
Dim strFile56 As String
Dim strRange56 As String
Dim strFile57 As String
Dim strRange57 As String
Dim strFile58 As String
Dim strRange58 As String
Dim strFile59 As String
Dim strRange59 As String
Dim strFile60 As String
Dim strRange60 As String
Dim strFile61 As String
Dim strRange61 As String
Dim strFile62 As String
Dim strRange62 As String
Dim strFile63 As String
Dim strRange63 As String
Dim strFile64 As String
Dim strRange64 As String
Dim strFile65 As String
Dim strRange65 As String
Dim strFile66 As String
Dim strRange66 As String
Dim strFile67 As String
Dim strRange67 As String
Dim strFile68 As String
Dim strRange68 As String
Dim strFile69 As String
Dim strRange69 As String
Dim strFile70 As String
Dim strRange70 As String
Dim strFile71 As String
Dim strRange71 As String
Dim strFile72 As String
Dim strRange72 As String
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
'Refering the Macro to the File Paths created by Excel for each countries banking spreadsheet from VICA
strFile = Range("Z2").Value
strFile2 = Range("Z3").Value
strFile3 = Range("Z4").Value
strFile4 = Range("Z5").Value
strFile5 = Range("Z6").Value
strFile6 = Range("Z7").Value
strFile7 = Range("Z8").Value
strFile8 = Range("Z9").Value
strFile9 = Range("Z10").Value
strFile10 = Range("Z11").Value
strFile11 = Range("Z12").Value
strFile12 = Range("Z13").Value
strFile13 = Range("Z14").Value
strFile14 = Range("Z15").Value
strFile15 = Range("Z16").Value
strFile16 = Range("Z17").Value
strFile17 = Range("Z18").Value
strFile18 = Range("Z19").Value
strFile19 = Range("Z20").Value
strFile20 = Range("Z21").Value
strFile21 = Range("Z22").Value
strFile22 = Range("Z23").Value
strFile23 = Range("Z24").Value
strFile24 = Range("Z25").Value
strFile25 = Range("Z26").Value
strFile26 = Range("Z27").Value
strFile27 = Range("Z28").Value
strFile28 = Range("Z29").Value
strFile29 = Range("Z30").Value
strFile30 = Range("Z31").Value
strFile31 = Range("Z32").Value
strFile32 = Range("Z33").Value
strFile33 = Range("Z34").Value
strFile34 = Range("Z35").Value
strFile35 = Range("Z36").Value
strFile36 = Range("Z37").Value
strFile37 = Range("Z38").Value
strFile38 = Range("Z39").Value
strFile39 = Range("Z40").Value
strFile40 = Range("Z41").Value
strFile41 = Range("Z42").Value
strFile42 = Range("Z43").Value
strFile43 = Range("Z44").Value
strFile44 = Range("Z45").Value
strFile45 = Range("Z46").Value
strFile46 = Range("Z47").Value
strFile47 = Range("Z48").Value
strFile48 = Range("Z49").Value
strFile49 = Range("Z50").Value
strFile50 = Range("Z51").Value
strFile51 = Range("Z52").Value
strFile52 = Range("Z53").Value
strFile53 = Range("Z54").Value
strFile54 = Range("Z55").Value
strFile55 = Range("Z56").Value
strFile56 = Range("Z57").Value
strFile57 = Range("Z58").Value
strFile58 = Range("Z59").Value
strFile59 = Range("Z60").Value
strFile60 = Range("Z61").Value
strFile61 = Range("Z62").Value
strFile62 = Range("Z63").Value
strFile63 = Range("Z64").Value
strFile64 = Range("Z65").Value
strFile65 = Range("Z66").Value
strFile66 = Range("Z67").Value
strFile67 = Range("Z68").Value
strFile68 = Range("Z69").Value
strFile69 = Range("Z70").Value
strFile70 = Range("Z71").Value
strFile71 = Range("Z72").Value
strFile72 = Range("Z73").Value
'Run Sub From Below to have the workbook open and copy and close the banking data spreadsheets from each country
GetDataFromClosedWorkbook strFile, "A2:K500", Range("A46"), True
GetDataFromClosedWorkbook strFile2, "A2:K500", Range("A546"), True
GetDataFromClosedWorkbook strFile3, "A2:K500", Range("A1047"), True
GetDataFromClosedWorkbook strFile4, "A2:K500", Range("A1548"), True
GetDataFromClosedWorkbook strFile5, "A2:K500", Range("A2049"), True
GetDataFromClosedWorkbook strFile6, "A2:K500", Range("A2550"), True
GetDataFromClosedWorkbook strFile7, "A2:K500", Range("A3051"), True
GetDataFromClosedWorkbook strFile8, "A2:K500", Range("A3552"), True
GetDataFromClosedWorkbook strFile9, "A2:K500", Range("A4053"), True
GetDataFromClosedWorkbook strFile10, "A2:K500", Range("A4554"), True
GetDataFromClosedWorkbook strFile11, "A2:K500", Range("A5055"), True
GetDataFromClosedWorkbook strFile12, "A2:K500", Range("A5556"), True
GetDataFromClosedWorkbook strFile13, "A2:K500", Range("A6057"), True
GetDataFromClosedWorkbook strFile14, "A2:K500", Range("A6558"), True
GetDataFromClosedWorkbook strFile15, "A2:K500", Range("A7059"), True
GetDataFromClosedWorkbook strFile16, "A2:K500", Range("A7560"), True
GetDataFromClosedWorkbook strFile17, "A2:K500", Range("A8061"), True
GetDataFromClosedWorkbook strFile18, "A2:K500", Range("A8562"), True
GetDataFromClosedWorkbook strFile19, "A2:K500", Range("A9063"), True
GetDataFromClosedWorkbook strFile20, "A2:K500", Range("A9564"), True
GetDataFromClosedWorkbook strFile21, "A2:K500", Range("A10065"), True
GetDataFromClosedWorkbook strFile22, "A2:K500", Range("A10566"), True
GetDataFromClosedWorkbook strFile23, "A2:K500", Range("A11067"), True
GetDataFromClosedWorkbook strFile24, "A2:K500", Range("A11568"), True
GetDataFromClosedWorkbook strFile25, "A2:K500", Range("A12069"), True
GetDataFromClosedWorkbook strFile26, "A2:K500", Range("A12570"), True
GetDataFromClosedWorkbook strFile27, "A2:K500", Range("A13071"), True
GetDataFromClosedWorkbook strFile28, "A2:K500", Range("A13572"), True
GetDataFromClosedWorkbook strFile29, "A2:K500", Range("A14073"), True
GetDataFromClosedWorkbook strFile30, "A2:K500", Range("A14574"), True
GetDataFromClosedWorkbook strFile31, "A2:K500", Range("A15075"), True
GetDataFromClosedWorkbook strFile32, "A2:K500", Range("A15576"), True
GetDataFromClosedWorkbook strFile33, "A2:K500", Range("A16077"), True
GetDataFromClosedWorkbook strFile34, "A2:K500", Range("A16578"), True
GetDataFromClosedWorkbook strFile35, "A2:K500", Range("A17079"), True
GetDataFromClosedWorkbook strFile36, "A2:K500", Range("A17580"), True
GetDataFromClosedWorkbook strFile37, "A2:K500", Range("A18081"), True
GetDataFromClosedWorkbook strFile38, "A2:K500", Range("A18582"), True
GetDataFromClosedWorkbook strFile39, "A2:K500", Range("A19083"), True
GetDataFromClosedWorkbook strFile40, "A2:K500", Range("A19584"), True
GetDataFromClosedWorkbook strFile41, "A2:K500", Range("A20085"), True
GetDataFromClosedWorkbook strFile42, "A2:K500", Range("A20586"), True
GetDataFromClosedWorkbook strFile43, "A2:K500", Range("A21087"), True
GetDataFromClosedWorkbook strFile44, "A2:K500", Range("A21588"), True
GetDataFromClosedWorkbook strFile45, "A2:K500", Range("A22089"), True
GetDataFromClosedWorkbook strFile46, "A2:K500", Range("A22590"), True
GetDataFromClosedWorkbook strFile47, "A2:K500", Range("A23091"), True
GetDataFromClosedWorkbook strFile48, "A2:K500", Range("A23592"), True
GetDataFromClosedWorkbook strFile49, "A2:K500", Range("A24093"), True
GetDataFromClosedWorkbook strFile50, "A2:K500", Range("A24594"), True
GetDataFromClosedWorkbook strFile51, "A2:K500", Range("A25095"), True
GetDataFromClosedWorkbook strFile52, "A2:K500", Range("A25596"), True
GetDataFromClosedWorkbook strFile53, "A2:K500", Range("A26097"), True
GetDataFromClosedWorkbook strFile54, "A2:K500", Range("A26598"), True
GetDataFromClosedWorkbook strFile55, "A2:K500", Range("A27099"), True
GetDataFromClosedWorkbook strFile56, "A2:K500", Range("A27600"), True
GetDataFromClosedWorkbook strFile57, "A2:K500", Range("A28101"), True
GetDataFromClosedWorkbook strFile58, "A2:K500", Range("A28602"), True
GetDataFromClosedWorkbook strFile59, "A2:K500", Range("A29103"), True
GetDataFromClosedWorkbook strFile60, "A2:K500", Range("A29604"), True
GetDataFromClosedWorkbook strFile61, "A2:K500", Range("A30105"), True
GetDataFromClosedWorkbook strFile62, "A2:K500", Range("A30606"), True
GetDataFromClosedWorkbook strFile63, "A2:K500", Range("A31107"), True
GetDataFromClosedWorkbook strFile64, "A2:K500", Range("A31608"), True
GetDataFromClosedWorkbook strFile65, "A2:K500", Range("A32109"), True
GetDataFromClosedWorkbook strFile66, "A2:K500", Range("A32610"), True
GetDataFromClosedWorkbook strFile67, "A2:K500", Range("A33111"), True
GetDataFromClosedWorkbook strFile68, "A2:K500", Range("A33612"), True
GetDataFromClosedWorkbook strFile69, "A2:K500", Range("A34113"), True
GetDataFromClosedWorkbook strFile70, "A2:K500", Range("A34614"), True
GetDataFromClosedWorkbook strFile71, "A2:K500", Range("A35115"), True
GetDataFromClosedWorkbook strFile72, "A2:K500", Range("A35616"), True
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub
Sub GetDataFromClosedWorkbook(SourceFile As String, SourceRange As String, _
    TargetRange As Range, IncludeFieldNames As Boolean)
Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset
Dim dbConnectionString As String
Dim TargetCell As Range, i As Integer
    dbConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};" & _
        "ReadOnly=1;DBQ=" & SourceFile
    Set dbConnection = New ADODB.Connection
    On Error GoTo InvalidInput
    dbConnection.Open dbConnectionString ' open the database connection
    Set rs = dbConnection.Execute("[" & SourceRange & "]")
    Set TargetCell = TargetRange.Cells(1, 1)
    If IncludeFieldNames Then
        For i = 0 To rs.Fields.Count - 1
            TargetCell.Offset(0, i).Formula = rs.Fields(i).Name
        Next i
        Set TargetCell = TargetCell.Offset(1, 0)
    End If
    TargetCell.CopyFromRecordset rs
    rs.Close
    dbConnection.Close ' close the database connection
    Set TargetCell = Nothing
    Set rs = Nothing
    Set dbConnection = Nothing
    On Error GoTo 0
    Exit Sub
InvalidInput:
    MsgBox "The source file or source range is invalid!", _
        vbExclamation, "Get data from closed workbook"
End Sub
'Redbaron

 

[wigi]

Consider as (more than) a starting point:

Sub shortercode()

    Dim rng As Range
    Dim l As Long
    
    l = 45 'instead of 46 I think
    
    For Each rng In Range("Z2:Z73")
        GetDataFromClosedWorkbook rng.Text, "A2:K500", Range("A" & l), True
        l = l + 501
    Next

End Sub

 

[redbaron06]

Thanks wigi - that is awesome! And very simple. Thanks for putting in the effort. This really helps me to understand the coding better.

I will tinker with the code and see if I can get it to put it in the next blank cell.