Dynamic String Variable Too Long for Range() Function Argument

[qaxooti]

Hey,

What I am trying to do is search a large database for certain data and paste that particular data to another sheet. So, a lot of non-contiguous rows will be selected (because all of the data cannot be sorted in a relevant order).

I have built a small script that essentially creates a string that will populate with whatever rows need to be copied over (see below). The problem is, when I try to pass that in the Range() function, the argument is too long for VBA (and thus it throws a 1094 error). Is there some easy way to linebreak the string so it is built in a way that the Range() function doesn't have an issue with? Like I said, this string variable is dynamic (the database is constantly changing and getting larger), so it will not be a fixed length every time.

<CODE>

' variable that details the rows that need to be copied over
temp = temp & i & ":" & i & ","

' selecting and copying the rows from above and pasting them onto sheet 1
ActiveSheet.Range(temp).Select
Selection.Copy
Sheets("Sheet1").Select
Sheets("Sheet1").Activate
Range("A1").Select
ActiveSheet.Paste

<CODE>

The error is thrown on the "ActiveSheet.Range(temp).Select" line, because the temp variable is too long.

I would greatly appreciate any help.

 

[Norie]

Why the long string?

Couldn't you combine all the rows using Union or something similar

Dim rng As Range
Dim I As Long
 
For I = 1 To 20 Step 2
 
     If rng Is Nothing Then
        Set rng = Rows(I)
     Else
        Set rng = Union(rng, Rows(I))
     End If
Next I
 
MsgBox rng.Address

Oops, just realised that might not work because of the mutliple ranges.

 

[Andrew Poulsom]

Why don't you build up the range using the Union method rather than creating a long string?

 

[qaxooti]

I'm not familiar with the Union function. I'll dig into it some.

Will the Union function play nice with non-contiguous rows?

Could anyone briefly describe how the Union function works and how that would work better/differently than the Range function?

 

[Norie]

qaxooti

Union will work fine with non-contiguous rows, it's the copy that won't.

That might even be the reason your having the problem rather than the long string.

Why not copy row by row?

 

[qaxooti]

The work around that I've done now is copying row by row. However, we are talking about copying over 2000+ rows. It takes nearly 5 minutes or so to complete and has to continually switch between the 2 sheets. This isn't an elegant solution. Plus, there will be quite a few more searches I want to do that will build other sheets as well using this same methodology. So, I'd like an elegant solution that would work.

Doesn't Union have similar restrictions in terms of string length? At least, the ranges that the Union takes in as parameters, don't they themselves have to be a certain length for VBA not to error?

For example, in the code below, if Rng1 contains too many rows, won't that in itself error out before we even reach the Union() code? Remember, the range of rows to be selected and copied is dynamic. It may be 2000 now, maybe 2500 later (after more entries are added to the database).

<CODE>

Dim Rng1, Rng2, Rng3 As Range

Set Rng1 = Range("A1,A3,A5,A7,A9,A11,A13,A15,A17,A19,A21")
Set Rng2 = Range("C1,C3,C5,C7,C9,C11,C13,C15,C17,C19,C21")
Set Rng3 = Range("E1,E3,E5,E7,E9,E11,E13,E15,E17,E19,E21")

Union(Rng1, Rng2, Rng3).Select

</CODE>

A work around I have thought about is maybe trying to only copy over 15 rows at a time (which is an ok length for the string variable) and then increment over to the next 15 rows and then copy those over. This would cut down the time significantly, but would require further tweaking and testing of the code (and isn't really a final solution).

I know that you can add a _ character to a string in VBA and then continue it on the next line. Essentially, it'd be great if I could somehow implement that _ character into the temp string while it's being built, so when the Range function takes in that string parameter, it knows that the argument is broken up over separate lines and it won't seize up because the string variable is too long. I've tried a few different ways of manipulating the temp string variable to accomplish this, but haven't been successful (I'm not even sure it's possible).

 

[Norie]

The code does not have to continually switch between sheets.

You rarely if ever need to use Select/Activate and using them does slow things down.

Can you post the current code and perhaps a short explanation of what it's meant to do?

 

[qaxooti]

The code below is rough and isn't exactly elegant, so excuse the lack of clarity and efficiency in the code.

The code below's purpose is to massively copy and paste everything in 1 motion. I deleted out the parts that would copy over everything row by row. If the below is confusing, feel free to ask me for clarification.

Sub BuildIOSheets()

Dim i As Long
Dim temp As String
Dim IOFirstRow As Long
Dim IOFinalRow As Long
Dim IOType As String
Dim IOAccessName As String
Dim SheetFinalRow As Long
Dim PasteFinalRow
Dim myRange As Range

Sheets("WW_DB").Select
ActiveSheet.Range("A1").Select
SheetFinalRow = Range("A65536").End(xlUp).Row

i = 1

'this loop determines the range of the data you are looking for.  For example, if the data 
'you seek is between rows 45 and 75, IOFirstRow would be 45 and IOFinalRow would be   
'75.  The structure of the data is fixed, the length is not.  SheetFinalRow is the total 
'amount of rows of data in the entire "WW_DB" spreadsheet.

Do While i <= SheetFinalRow
 
IOType = ActiveSheet.Range("a" + CStr(i)).Value

If IOType = ":IODisc" Then
    IOFirstRow = CStr(i + 1)
End If

If IOType = ":IOInt" Then
    IOFinalRow = CStr(i - 1)
End If

i = i + 1

Loop


' this loop iterates through that range of data (IOFirstRow and IOFinalRow) and looks for 
'data that matches the IF condition.  If that data is matched, you want to copy over that 
'row to temp.  Once the loop goes through the next row and the IF condition is met, you 
'essentially concatenate that row with the previous row, and so on and so forth.  So, you 
'can imagine, 2000+ rows can build quite a lengthy temp variable

i = IOFirstRow

Do While i <= IOFinalRow

IOType = Sheets("WW_DB").Range("a" + CStr(i)).Value
IOAccessName = Sheets("WW_DB").Range("n" + CStr(i)).Value

If IOAccessName = "atkPLC0_Active" Or IOAccessName = "PLC0_Active" Or IOAccessName = "PLC0_Alarm" Or IOAccessName = "PLC0_All" Then
    
    temp = temp & i & ":" & i & ","
    
End If

i = i + 1

Loop

'this bit of code takes off the end comma off of the final temp string.  i've tested that the 
'temp statement is in the correct format for the Range() function by pasting that variable 
'in a cell for monitoring purposes.  I've also tested this method on a much smaller sample 
'data set (maybe only 10 rows) and it works correctly because only 10 or so rows are 
'being set in the temp variable.

temp = Left(temp, Len(temp) - 1)

'this line is where it errors.  i even tried to manually input the entire string into the  
'Range() function (copying it from the pasted cell) and it was clearly too long.  That's 
'what led me to the conclusion that the length of the string is the issue for VBA

ActiveSheet.Range(temp).Select
Selection.Copy
Sheets("Sheet1").Select
Sheets("Sheet1").Activate
Range("A1").Select
ActiveSheet.Paste

End Sub