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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

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
36,960
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. 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
36,960
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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
36,960
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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
36,960
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. 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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,810
Messages
5,661,025
Members
418,610
Latest member
nmnk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top