Seems I found a limit to the length of the range.Address of 256 characters.
My code basically opens several workbooks (individually) and performs searches through the csv files for lines that meet specific criteria and copies the data (entire row) to the current workbook. Instead of copying each Row as it finds it, the code makes a list and add it to a range. The reason behind this process is to reduce the amount of time taken to copy each line individually. Row by row copying can take several minutes, copying the range takes only seconds. This all works fine until there appears to be too many cells in the ranges. If the range.Address exceeds 256 characters, I get the following error. Error message:
Run-time error '1004': Application-defined or object-defined error
Relevant code:
Dim rngToAdd As Range
Set importWB = Workbooks.Open(FldrName & "\various.csv")
' start with header row
Set rngToAdd = importWB.Sheets("hosts").Cells(1, 1)
For i Loop (through csv file)
If match..
Set rngToAdd = Union(rngToAdd, Cells(i, 1))
End If
next i
' The line below errors when rngToAdd.Address reaches 257 characters or more
importWB.Sheets("services").Range(rngToAdd.Address).EntireRow.Copy _
thisWB.Worksheets("services.csv").Range("A1")
Content of rngToAdd.Address when error occurs. (257 characters)
$A$1,$A$174:$A$177,$A$188,$A$243,$A$282,$A$557:$A$559,$A$576:$A$577,$A$602:$A$609,$A$612:$A$615,$A$667:$A$675,$A$719,$A$764:$A$765,$A$768:$A$777,$A$779:$A$784,$A$786:$A$788,$A$791:$A$792,$A$797:$A$798,$A$800,$A$805:$A$806,$A$808:$A$809,$A$869:$A$870,$A$879
One work around I found was to use the REPLACE function and remove the dollar signs.
tmpAddress = Replace(rngToAdd.Address, "$", "")
importWB.Sheets("services").Range(tmpAddress).EntireRow.Copy _
DHSwb.Worksheets("services.csv").Range("A1")
Is there a better way to fix this apparent 256 character limit? Or is it really a character limit and some other issue? I don't want this code to error out in the future if more ranges (cells) are found.
Excel for Office 365 MSO (16.0.12527.21230) 64-bit