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. Th
data goes all the way to row 149. In Excel if I look for the last ro
it goes to 149, so does this code. For instance A:L might only have 4
rows. I want to just select those 45 rows and not the rows with hidde
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 i
to look in just columns A-L

--
lori
-----------------------------------------------------------------------
loric's Profile: http://www.excelforum.com/member.php...fo&userid=3392
View this thread: http://www.excelforum.com/showthread.php?threadid=53698

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

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

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

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

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

thanks so much for your help.


--
loric
------------------------------------------------------------------------
loric's Profile: http://www.excelforum.com/member.php...o&userid=33920
View this thread: http://www.excelforum.com/showthread...hreadid=536980