Concatination error in macro
 

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

Concatination error in macro
 

Hi Constantly,

Try replacing

Set rngToCopy = "A1:U" & LastRowUsed


with

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


---
Regards,
Norman


"Constantly Amazed" wrote in
message ...
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

Concatination error in macro
 

You nearly got it, try this:-

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

Mike

"Constantly Amazed" wrote:

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

Concatination error in macro
 

oops typo

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

Mike

"Mike H" wrote:

You nearly got it, try this:-

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

Mike

"Constantly Amazed" wrote:

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

Concatination error in macro
 

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



Constantly Amazed wrote:

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

--

Dave Peterson

Concatination error in macro
 

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)

--
Regards,
Tom Ogilvy




"Constantly Amazed" wrote:

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

Concatination error in macro
 

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.

"Tom Ogilvy" wrote:

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)

--
Regards,
Tom Ogilvy




"Constantly Amazed" wrote:

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

Concatination error in macro
 

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

--
Regards,
Tom Ogilvy

"Constantly Amazed" wrote:

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.

"Tom Ogilvy" wrote:

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)

--
Regards,
Tom Ogilvy




"Constantly Amazed" wrote:

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

Concatination error in macro
 

Tom

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

G

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"Constantly Amazed" wrote:

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.

"Tom Ogilvy" wrote:

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)

--
Regards,
Tom Ogilvy




"Constantly Amazed" wrote:

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