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
|
Similar Threads
