Selecting Used Range

[loric]

I have the following code I am trying to edit:

Public Sub SelectData()

Workbooks(CurName).Activate
Range("A1").Activate

ActiveSheet.UsedRange.Select
rowscount = Selection.Rows.Count

Set CopyArea = Range("A1", ("L" & rowscount))

CopyArea.Select
Get_Col_Widths

End Sub

There are some hidden columns to the right of L that contain data. The data goes all the way to row 149. In Excel if I look for the last row it goes to 149, so does this code. For instance A:L might only have 45 rows. I want to just select those 45 rows and not the rows with hidden data in them. Otherwise I get additional pages with no data in them. Can this be done? Is there a way in the Slection.Rows.Count to limit it to look in just columns A-L?

[Zack Barresse]

Hi there,

Here are some examples which produce different results ...


Sub testme()
Dim LastRow As Long, LastCol As Long
LastRow = Cells.Find("*", after:=Cells(1, 1), searchorder:=xlByRows,
searchdirection:=xlPrevious).Row
LastCol = Cells.Find("*", after:=Cells(1, 1), searchorder:=xlByColumns,
searchdirection:=xlPrevious).Column
MsgBox "Row: " & LastRow & vbNewLine & "Col: " & LastCol
End Sub

Sub testme2()
Dim LastRow As Long, LastCol As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
MsgBox "Row: " & LastRow & vbNewLine & "Col: " & LastCol
End Sub


You could probably shorten your code to ...


Public Sub SelectData()
With Workbooks(CurName).Sheets("Sheet1") 'assumed..
Set CopyArea = .Range("A1", "L" & .Cells(.Rows.Count,
1).End(xlUp).Row)
End With
CopyArea.Select
Get_Col_Widths 'not sure what this is..
End Sub


HTH

--
Regards,
Zack Barresse, aka firefytr



"loric" <[email protected]> wrote in
message news:[email protected]...
>
> I have the following code I am trying to edit:
>
> Public Sub SelectData()
>
> Workbooks(CurName).Activate
> Range("A1").Activate
>
> ActiveSheet.UsedRange.Select
> rowscount = Selection.Rows.Count
>
> Set CopyArea = Range("A1", ("L" & rowscount))
>
> CopyArea.Select
> Get_Col_Widths
>
> End Sub
>
> There are some hidden columns to the right of L that contain data. The
> data goes all the way to row 149. In Excel if I look for the last row
> it goes to 149, so does this code. For instance A:L might only have 45
> rows. I want to just select those 45 rows and not the rows with hidden
> data in them. Otherwise I get additional pages with no data in them.
> Can this be done? I there a way in the Slection.Rows.Count to limit it
> to look in just columns A-L?
>
>
> --
> loric
> ------------------------------------------------------------------------
> loric's Profile:
> http://www.excelforum.com/member.php...o&userid=33920
> View this thread: http://www.excelforum.com/showthread...hreadid=536980
>

[loric]

Zach,

Thank you so much for taking the time to reply to me. I tried your first sub and it does the same thing, it finds the last row that includes the hidden rows outside of column L. The second one is close, it finds the rows but is missing a few. This worksheet has some items with a sub total, discount, and total in columns J-L. It finds the last row just above the totals and does not include them.

On the third example I am getting a "Subscript out of range" error on the line - With Workbooks(CurName).Sheets("Sheet1")

I tried just changing the copyarea in the original code to :

Workbooks(CurName).Activate
Range("A1").Activate

ActiveSheet.UsedRange.Select
rowscount = Selection.Rows.Count

'Set CopyArea = Range("A1", ("L" & rowscount))
Set CopyArea = .Range("A1", "L" & .Cells(.Rows.Count, 1).End(xlUp).Row)

CopyArea.Select
Get_Col_Widths 'used to set the column width, this is being copied to a new workbook

I get an error "Invalid or unqualified reference " and it highlights .rows

I am editing someone else's code and trying to learn this as I go.

Lori

[Zack Barresse]

The first code produces the correct results (which is not for you in this
situation, just fyi), the second code will produce the right results when we
change the column letter. It's basically saying go to the end of the column
(Ctrl + Down as needed, generally row 65536), then press Ctrl + Up Arrow, to
hit the last row with data in it (and hidden columns/rows are ignored). So
you could change it to this ...


Sub testme2()
Dim LastRow As Long, LastCol As Long
LastRow = Cells(Rows.Count, 10).End(xlUp).Row 'Note: 10 = the column to
come up, 1=A, 2=B...10=J, etc
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column 'likewise, 1 is
the row.
MsgBox "Row: " & LastRow & vbNewLine & "Col: " & LastCol
End Sub


This line should be used still...


With Workbooks(CurName).Sheets("Sheet1")


You just need to make sure the sheet name changes from "Sheet1" to the name
of the sheet you have. Try that and let us know if it works for you.

HTH

--
Regards,
Zack Barresse, aka firefytr



"loric" <[email protected]> wrote in
message news:[email protected]...
>
> Zach,
>
> Thank you so much for taking the time to reply to me. I tried your
> first sub and it does the same thing, it finds the last row that
> includes the hidden rows outside of column L. The second one is close,
> it finds the rows but is missing a few. This worksheet has some items
> with a sub total, discount, and total in columns J-L. It finds the last
> row just above the totals and does not include them.
>
> On the third example I am getting a "Subscript out of range" error on
> the line - With Workbooks(CurName).Sheets("Sheet1")
>
> I tried just changing the copyarea in the original code to :
> Workbooks(CurName).Activate
> Range("A1").Activate
>
> ActiveSheet.UsedRange.Select
> rowscount = Selection.Rows.Count
>
> 'Set CopyArea = Range("A1", ("L" & rowscount))
> Set CopyArea = .Range("A1", "L" & .Cells(.Rows.Count,
> 1).End(xlUp).Row)
>
> CopyArea.Select
> Get_Col_Widths 'used to set the column width, this is being copied to a
> new workbook
> I get an error "Invalid or unqualified reference " and it highlights
> rows
>
> I am editing someone else's code and trying to learn this as I go.
>
> Lori
>
>
> --
> loric
> ------------------------------------------------------------------------
> loric's Profile:
> http://www.excelforum.com/member.php...o&userid=33920
> View this thread: http://www.excelforum.com/showthread...hreadid=536980
>

[loric]

thanks so much for your help.