Excel range issue

[tmccar]

Set xlbook = GetObject("C:\07509\LB_RACKTMC.xlsx")
Set xlSheet = xlbook.Sheets("LB RACK")
Set src = xlSheet.Range("f1..f3").CurrentRegion.SpecialCells(xlCellTypeVisible)
Set sh = xlbook.Sheets.Add

src.Copy sh.Range("a1")
m = sh.Range("a1").CurrentRegion

For ro = LBound(m) + 1 To UBound(m)

Debug.Print LBound(m), UBound(m)


I want to copy a range "f1 to f3" from sheet "LB RACK" to a new sheet ("Sheet1")
For some reason, the Ubound(m) value in this VBA code is showing as 1245. Should this not be 3, as the range I am copying is ("f1..f3")?

 

[MARK858]

If all you want to copy is F1:F3 why are you using currentregion (and specialcells visible for that matter)?

 

[tmccar]

If all you want to copy is F1:F3 why are you using currentregion (and specialcells visible for that matter)?

Ok, I will try without these when I get back to the office.

 

[MARK858]

Do you understand what CurrentRegion and SpecialCells(xlCellTypeVisible) do?

 

[MARK858]

Just in case you aren't clear on what they do, open a new workbook and copy the below into Sheet1

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	aaa	bbb				aaa				aaa
2	aaa					aaa				aaa
3	aaa					aaa				aaa
4							bbb
5											bbb
6
Sheet1

The CurrentRegion is from your starting range until the last cell contiguous to the range, basically the region is a range bounded by blank rows and blank columns.

with the layout above run the code below and hopefully you will see what it does.

Sub test()
    Dim X As Range, Y As Range, Z As Range
    
    Set X = Range("A1:A3").CurrentRegion
    X.Select
    MsgBox X.Address

    Set Y = Range("F1:F3").CurrentRegion
    Y.Select
    MsgBox Y.Address

    Set Z = Range("J1:J3").CurrentRegion
    Z.Select
    MsgBox Z.Address

End Sub

I will try and demonstrate specialcells visible in the next post to save messing up the screenshot

 

[MARK858]

Ok, SpecialCells(xlCellTypeVisible)...

Basically it does what it says on the tin. It references only the cells you can see. So for instance it doesn't reference any rows or columns hidden either manually or by code including any hidden by using a filter.

As a basic demonstration copy the screenshot below to Sheet2

Excel Workbook
	A	B	C	D	E	F
1	1	1	1	1
2	2	2	2	2
3	3	3	3	3
4
5
6
Sheet2

then run the code below

Sub VISCHECK1()
Sheets("Sheet3").Cells.Delete
Sheets("Sheet2").Rows(2).Hidden = False
Sheets("Sheet2").Rows(2).Hidden = True
Sheets("Sheet2").Range("A1:D3").Copy Sheets("Sheet3").Range("A1")
End Sub

now look at Sheet3 (in particular row 2).
Now run the code below and then look at Sheet3.

Sub VISCHECK2()
Sheets("Sheet3").Cells.Delete
Sheets("Sheet2").Rows(2).Hidden = False
Sheets("Sheet2").Rows(2).Hidden = True
Sheets("Sheet2").Range("A1:D3").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("A1")
End Sub

 

[tmccar]

Ok, SpecialCells(xlCellTypeVisible)...

Basically it does what it says on the tin. It references only the cells you can see. So for instance it doesn't reference any rows or columns hidden either manually or by code including any hidden by using a filter.

As a basic demonstration copy the screenshot below to Sheet2

Sheet2

	A	B	C	D	E	F

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

then run the code below

Sub VISCHECK1()
Sheets("Sheet3").Cells.Delete
Sheets("Sheet2").Rows(2).Hidden = False
Sheets("Sheet2").Rows(2).Hidden = True
Sheets("Sheet2").Range("A1:D3").Copy Sheets("Sheet3").Range("A1")
End Sub

now look at Sheet3 (in particular row 2).
Now run the code below and then look at Sheet3.

Sub VISCHECK2()
Sheets("Sheet3").Cells.Delete
Sheets("Sheet2").Rows(2).Hidden = False
Sheets("Sheet2").Rows(2).Hidden = True
Sheets("Sheet2").Range("A1:D3").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet3").Range("A1")
End Sub

Mark - Thanks for your helpful explanations
Tom