Set range on spreadsheet

G2K

Active Member
Joined
May 29, 2009
Messages
355
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

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try:-
Code:
[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
 
Last edited:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,389
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
How did you declare rng? You need to specify that it's an OWC range:
Code:
Dim rng as OWC11.Range
for example.
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Rory, You keep telling me, But I can't seem to get the message.
:- "Read the Thread Properly" before answering.
Regards Mick
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,389
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Takes a lot of the fun out of it though... ;)
 

G2K

Active Member
Joined
May 29, 2009
Messages
355
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.
 
Last edited:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,389
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
What code are you using and where does it error?
 

G2K

Active Member
Joined
May 29, 2009
Messages
355
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,389
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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

Active Member
Joined
May 29, 2009
Messages
355
Thanks Rory,

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

Regards
 

Forum statistics

Threads
1,082,569
Messages
5,366,360
Members
400,886
Latest member
Fchel

Some videos you may like

This Week's Hot Topics

Top