Set range on spreadsheet

[G2K]

Hi All,

I am using Spreadsheet control on User form and i just want to set Range as we do for worksheets. but unfortunately it is not working here. and generates the error

Set rng = Spreadsheet1.Range("A2:A" & Spreadsheet1.Range("A" & Spreadsheet1.Rows.Count).End(xlUp).Row)

here spreadsheet1 is spreadsheet control and Error is "Type Mismatch"

Thanks

 

[MickG]

Try:-

[COLOR="Navy"]With[/COLOR] Sheets("Sheet1")
[COLOR="Navy"]Set[/COLOR] Rng = .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
'[COLOR="Green"][B]Or:- Set Rng = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))[/B][/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]

Regards Mick

 

[RoryA]

How did you declare rng? You need to specify that it's an OWC range:

Dim rng as OWC11.Range

for example.

 

[MickG]

Rory, You keep telling me, But I can't seem to get the message.
:- "Read the Thread Properly" before answering.
Regards Mick

 

[RoryA]

Takes a lot of the fun out of it though...

 

[G2K]

sorry for replying late, i was on leave.

i am still strugling for my desired output.

i want to set range on Spreadsheet Object,then apply filter and count viewable range based on filter criteria. but unfortunately i am getting a message ther object does not support this property.

i know it can be done on worksheet, but i want same operation on spreadsheet control.

let total rows are 100. when i apply filter using any criteria on any colume the viewable range can be 10/12/15 row.

i want to count the viewable range and perform some calculation based on this data.


thanks.

 

[RoryA]

What code are you using and where does it error?

 

[G2K]

Code:-

For i = 1 To Spreadsheet3.Range("A2:A" & Spreadsheet3.Cells(Spreadsheet3.Rows.Count, 1).End(xlUp).Row).Count
Sql = "Insert into tblValidate(Pbrefno,Validation)values(" & Spreadsheet3.Cells(i + 1, 1).Value & ",True)"
Con.BeginTrans
Con.Execute Sql
Con.CommitTrans
Next

Code given above is working fine.

i want to perform the same operation on selected range like -


Dim rng As owc11.Range

Set rng = Spreadsheet3.Range("A2:A" & Spreadsheet3.Range("A" & Spreadsheet3.Rows.Count).End(xlUp).Row)

For I =1 to rng.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1
'Perform operation here.......
Next

Thanks

 

[RoryA]

You can't do that, I'm afraid, as there is no SpecialCells method for an OWC range. You would have to loop and check if they are hidden.

 

[G2K]

Thanks Rory,

is there any ohter way out here to perform the same task.

Regards