Concatination error in macro

[Guest]

Hi

In order to select an area limited by the number of rows in column A that
contain data in them I was provided with the following code:

Sub Select_area()

Dim LastRowUsed As Integer
Dim rngToCopy As Range

LastRowUsed = Range("A" & Rows.Count).End(xlUp).Row

Set rngToCopy = "A1:U" & LastRowUsed

End Sub

However, when I run this I get a compelation error: type mismatch and the
debugger highlights the & in the Set rngToCopy instruction which I read as
concatinating the number returned with the U to create a range.

Can anyone explain why this has failed and more importantly how to fix it?

As always thanks for any help

 

[Norman Jones]

Hi Constantly,

Try replacing

Set rngToCopy = "A1:U" & LastRowUsed

with

Set rngToCopy = Range("A1:U" & LastRowUsed)

 

[Guest]

You nearly got it, try this:-

Set rngToCopy = Range("A1:U5" & LastRowUsed)

Mike

 

[Guest]

oops typo

Set rngToCopy = Range("A1:U" & LastRowUsed)

Mike

 

[Dave Peterson]

Set rngToCopy = "A1:U" & LastRowUsed
should be something like:

Set rngToCopy = range("A1:U" & LastRowUsed)
or
Set rngToCopy = activesheet.range("A1:U" & LastRowUsed)

And I'd use
dim LastRowUsed as Long

 

[Guest]

If this is the same situation you described yesterday, then that solution
wouldn't work to your specifications anyway. Here is a correction to what I
previously suggested:

Dim lastrow as Long, realLastRow as Long
Dim rngtocopy as Range
lastrow = cells(rows.count,1).End(xlup).row '<==
for i = lastrow to 4 step -1
if len(trim(cells(i,1).text)) > 0 then
reallastrow = i
exit for
end if
Next
Set rngToCopy = Range("A4:A" & reallastrow)

 

[Guest]

Hi Tom

Thanks for that. As your previous reply was some pages back I wasn't sure
if you would pick up on the error so I was trying to modify some other code
in the meantime. It works fine now when I put in a column reference.

If it is not too much trouble how can the code be further modified so it
looks for the last column which contains data as well as the last row.

Sorry if my second post caused any inconvenience.

 

[Guest]

Uses column A to determine lastrow and row 1 to determine last column:

Sub AAA()
Dim lastrow As Long, realLastRow As Long
Dim lastColumn As Long, realLastColumn As Long
Dim rngtocopy As Range, i As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row '<==
realLastRow = 4
For i = lastrow To 4 Step -1
If Len(Trim(Cells(i, 1).Text)) > 0 Then
realLastRow = i
Exit For
End If
Next
lastColumn = Cells(1, "IV").End(xlToLeft).Column
realLastColumn = lastColumn
For i = lastColumn To 1 Step -1
If Len(Trim(Cells(1, i).Text)) > 0 Then
realLastColumn = i
Exit For
End If
Next
Set rngtocopy = Range("A4", Cells(realLastRow, realLastColumn))
rngtocopy.Select
End Sub

 

[Guest]

Tom

Thank you very much. I often have to select large areas of data so this
will prove extremely useful.

G