selecting rows using a variable

I am using excel 2003 and I need to select rows using a variable.  Here is 
what I have now:

Dim name As String
name = Range("e800")

ActiveWorkbook.Sheets("individual stats").Range("a1:a1540").Select
Selection.EntireRow.Hidden = True

Dim Start As Integer
Dim finish As Integer

Start = Application.WorksheetFunction.Lookup(name, Range("Q800:Q881"), 
Range("t800:t881"))
finish = Application.WorksheetFunction.Lookup(name, Range("Q800:Q881"), 
Range("u800:u881"))

ActiveWorkbook.Sheets("individual stats").Rows(Start, finish).Select


Selection.EntireRow.Hidden = False

I verifed that in the case I was testing, start = 81 and finish = 100.  With 
this code I get "Application-defined or object-defined error"  with the row 
selection line highlighted.
 
any help would be appreciated
0
Utf
2/11/2010 2:16:01 PM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
808 Views

Similar Articles

[PageSpeed] 7

Hi,

Note your variables now declared as LONG and that we are no longer selecting 
anything

Dim name As String
Dim Start As Long
Dim finish As Long
name = Range("e800").Value
ActiveWorkbook.Sheets("individual stats").Rows(1).EntireRow.Hidden = True
Start = Application.WorksheetFunction.Lookup(name, _
Range("Q800:Q881"), Range("t800:t881"))
finish = Application.WorksheetFunction.Lookup(name, _
Range("Q800:Q881"), Range("u800:u881"))
ActiveWorkbook.Sheets("individual stats").Rows(Start & ":" & _
finish).EntireRow.Hidden = False


-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"bigjim" wrote:

> I am using excel 2003 and I need to select rows using a variable.  Here is 
> what I have now:
> 
> Dim name As String
> name = Range("e800")
> 
> ActiveWorkbook.Sheets("individual stats").Range("a1:a1540").Select
> Selection.EntireRow.Hidden = True
> 
> Dim Start As Integer
> Dim finish As Integer
> 
> Start = Application.WorksheetFunction.Lookup(name, Range("Q800:Q881"), 
> Range("t800:t881"))
> finish = Application.WorksheetFunction.Lookup(name, Range("Q800:Q881"), 
> Range("u800:u881"))
> 
> ActiveWorkbook.Sheets("individual stats").Rows(Start, finish).Select
> 
> 
> Selection.EntireRow.Hidden = False
> 
> I verifed that in the case I was testing, start = 81 and finish = 100.  With 
> this code I get "Application-defined or object-defined error"  with the row 
> selection line highlighted.
>  
> any help would be appreciated
0
Utf
2/11/2010 2:29:03 PM
That did it.  Thanks, I really appreciate it.

Jim

"Mike H" wrote:

> Hi,
> 
> Note your variables now declared as LONG and that we are no longer selecting 
> anything
> 
> Dim name As String
> Dim Start As Long
> Dim finish As Long
> name = Range("e800").Value
> ActiveWorkbook.Sheets("individual stats").Rows(1).EntireRow.Hidden = True
> Start = Application.WorksheetFunction.Lookup(name, _
> Range("Q800:Q881"), Range("t800:t881"))
> finish = Application.WorksheetFunction.Lookup(name, _
> Range("Q800:Q881"), Range("u800:u881"))
> ActiveWorkbook.Sheets("individual stats").Rows(Start & ":" & _
> finish).EntireRow.Hidden = False
> 
> 
> -- 
> Mike
> 
> When competing hypotheses are otherwise equal, adopt the hypothesis that 
> introduces the fewest assumptions while still sufficiently answering the 
> question.
> 
> 
> "bigjim" wrote:
> 
> > I am using excel 2003 and I need to select rows using a variable.  Here is 
> > what I have now:
> > 
> > Dim name As String
> > name = Range("e800")
> > 
> > ActiveWorkbook.Sheets("individual stats").Range("a1:a1540").Select
> > Selection.EntireRow.Hidden = True
> > 
> > Dim Start As Integer
> > Dim finish As Integer
> > 
> > Start = Application.WorksheetFunction.Lookup(name, Range("Q800:Q881"), 
> > Range("t800:t881"))
> > finish = Application.WorksheetFunction.Lookup(name, Range("Q800:Q881"), 
> > Range("u800:u881"))
> > 
> > ActiveWorkbook.Sheets("individual stats").Rows(Start, finish).Select
> > 
> > 
> > Selection.EntireRow.Hidden = False
> > 
> > I verifed that in the case I was testing, start = 81 and finish = 100.  With 
> > this code I get "Application-defined or object-defined error"  with the row 
> > selection line highlighted.
> >  
> > any help would be appreciated
0
Utf
2/11/2010 2:59:02 PM
Glad i could help and thanks for the feedback
-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"bigjim" wrote:

> That did it.  Thanks, I really appreciate it.
> 
> Jim
> 
> "Mike H" wrote:
> 
> > Hi,
> > 
> > Note your variables now declared as LONG and that we are no longer selecting 
> > anything
> > 
> > Dim name As String
> > Dim Start As Long
> > Dim finish As Long
> > name = Range("e800").Value
> > ActiveWorkbook.Sheets("individual stats").Rows(1).EntireRow.Hidden = True
> > Start = Application.WorksheetFunction.Lookup(name, _
> > Range("Q800:Q881"), Range("t800:t881"))
> > finish = Application.WorksheetFunction.Lookup(name, _
> > Range("Q800:Q881"), Range("u800:u881"))
> > ActiveWorkbook.Sheets("individual stats").Rows(Start & ":" & _
> > finish).EntireRow.Hidden = False
> > 
> > 
> > -- 
> > Mike
> > 
> > When competing hypotheses are otherwise equal, adopt the hypothesis that 
> > introduces the fewest assumptions while still sufficiently answering the 
> > question.
> > 
> > 
> > "bigjim" wrote:
> > 
> > > I am using excel 2003 and I need to select rows using a variable.  Here is 
> > > what I have now:
> > > 
> > > Dim name As String
> > > name = Range("e800")
> > > 
> > > ActiveWorkbook.Sheets("individual stats").Range("a1:a1540").Select
> > > Selection.EntireRow.Hidden = True
> > > 
> > > Dim Start As Integer
> > > Dim finish As Integer
> > > 
> > > Start = Application.WorksheetFunction.Lookup(name, Range("Q800:Q881"), 
> > > Range("t800:t881"))
> > > finish = Application.WorksheetFunction.Lookup(name, Range("Q800:Q881"), 
> > > Range("u800:u881"))
> > > 
> > > ActiveWorkbook.Sheets("individual stats").Rows(Start, finish).Select
> > > 
> > > 
> > > Selection.EntireRow.Hidden = False
> > > 
> > > I verifed that in the case I was testing, start = 81 and finish = 100.  With 
> > > this code I get "Application-defined or object-defined error"  with the row 
> > > selection line highlighted.
> > >  
> > > any help would be appreciated
0
Utf
2/11/2010 3:00:01 PM
Reply:

Similar Artilces:

How do I get a filter to "UPDATE" the rows selected?
Hey there! I am using Excel 2002 and I have a speadsheet that has prices in it. If I have the filter set to (the filter criteria is much more complex than this though) prices for items that cost >$5.00. Of I change an item to 4.59, it should not be seen. How can I get it to re-update the rows shown, with out releaseing the filter and resetting it? Is there something like refresh.filter or anything like that? Thanks Phil AFAIK you must release and reset but a macro with a worksheet_change event could trigger this for you so that it would happen automatically. -- Don Guillett SalesAid...

Select last value
I am trying to select the last (bottom) value on a one-column list. I am using the COUNT function to designate the bottom value that is not zero, and the CHOOSE function to select the designated value. But, I can't make that work. Help appreciated. try =match(a number larger than possible,your range) -- Don Guillett SalesAid Software donaldb@281.com "Carl" <c@invalid.com> wrote in message news:eciLEKsvFHA.3236@TK2MSFTNGP14.phx.gbl... > I am trying to select the last (bottom) value on a one-column list. I am > using the COUNT function to designate the bottom va...

ProbleM: when I restore a mailbox using Exmerge with a pst file, nothing is transferred.
Hi, I am practising Exmerge for a big remote site migration in a couple of weeks. One thing I dont understand is that I can backup one test mailbox fine using Exmerge (I know this works, as I have opened the mailbox pst file within outlook and everything is there), but when I perform the restore using the pst file, nothing happens. There is no error messages, and Emerge goes through the motions (though it finishes supsiciously quick), but when I open the mailbox, no emails have been restored. Although it is great that Exmerge is working for the backup part of the stage, I am disappointed it i...

Using Word 2003 in Vista: Opening dictionary shuts down Word
This is a problem in Vista; it did not occur when I used Word 2003 in XP. Whenever I try to open the dictionary in Word 2003, either by clicking its icon, or hitting Alt+click over a word, Word shuts down. Vista Business, Service Pack 2 Thinkpad T400, Intel Core 2 Duo CPU, 3GB RAM ...

Switching companies using SQL Passthrough
I have an application that uses SQL_Passthrough. As part of the code you must execute a statement that uses the appropriate database. The code looks like this: set SQL_Statements to "use MYDB"; status = SQL_Execute(SQL_connection, field SQL_Statements); This works fine, but my application can be used for any number of databases. At first, I modified it to use the Dex.ini file, which works. Here is the modification: dbname = Defaults_Read("SQLDB"); dbopencommand = "use " + dbname; set SQL_Statements to dbopencommand; status = SQL_Execute(SQL_connection,...

CtreeCtrl multiple selection
Hi, Is there any simple way (samples) to set a CTreeCtrl has multiple selection feature? Thanks, Chi Try these: http://www.techsoft.no/bendik/ http://www.codeguru.com/Cpp/controls/treeview/misc-advanced/article.php/c723 http://www.codeguru.com/Cpp/controls/treeview/misc-advanced/article.php/c629 -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "Chi" <anonymous@discussions.microsoft.com> wrote in message news:21bd01c4aa63$2e355d70$a501280a@phx.gbl... > Hi, > > Is there any simple way (samples) to set a CTreeCtrl has > multiple selection feature? Thanks, > &...

Old emails not displayed (Multiple PC's using same inbox)
I have 2 machines running XP and Outlook 2000. Both mahcines are setup to using the same Exchange Server email account. When i open outlook in one machine all mail are visable is the inbox, and new mail is received correctly. But if i opne Outlook on the second machine my inbox is empty. Any new mails will appear quickly then disappear. I cannot get the second machine to display old emails. If I leave the first machine off, all new mail will stay in the second machines inbox, but if I open outlook on the second machine all mail disappears and is shown in the first machines inbox. A...

Using namespaces? I've some messy nested contexts that I want to clean up...
Hi, I've got the following code structure Class A { ... private: Class B { public: enum C { ENUM X } C MyVar; C MyFunc() } } So for function definitions in B I have to write A::B::C A::B::MyFunc() and for objects of B in A if(pb->MyVar==B::ENUM_X) It's all just a bit messy. Isn't it. Someone please help. Regards. ...

Formula to process 3 cells using IF statements
I have 3 columns of experimental data (C:E). Row 30 contains the sums (C30:E30). I need a formula that will examine the three sums and return the column number that has the lowest sum. If more than one column is lowest, select one randomly. Example: C30 D30 E30 Result 10 11 12 1 (C) 22 20 21 2 (D) 32 31 30 3 (E) 40 41 40 Randomly select 1 or 3 51 50 50 Randomly select 2 or 3 60 60 60 Randonly select 1, 2, or 3 Can this be done with IF statements or do I need to write a macro? Well, this is a bit cumbersome, but it se...

Combining second row to first row
How do I make this: John Edwards 24 Main St NY NY 08000 (all data is in seperate cells) Look like this: John Edwards 24 Main St NY, NY 08000 (Each cell moved to top line) There are multiple rows of this data. Thanks try this Sub moverow() For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -2 Range(Cells(i, 1), Cells(i, 10)).Copy Cells(i - 1, 2) Rows(i).Delete Next End Sub -- Don Guillett SalesAid Software donaldb@281.com "LisaJ27" <LisaJ27@discussions.microsoft.com> wrote in message news:3CFDD53F-0D38-4117-AFAD-D65414BB86FB@microsoft.c...

printing row headings
I'd like to print the row headings on each page without printing the column headings. the rat wrote: > I'd like to print the row headings on each page without printing the column > headings. To: The Rat File | Page Setup | Sheet Tab On this tab, for "Print Area" select all but the row with the headings for "Rows to repeat at top" select the row with the headings Check results with "Print Preview" button Texas Handly ...

how do i enter data for a # of years using a formula?
i am working on excel and the book asks that i enter data s=using formulas for specifically the last three years of what i am referencing to. and i have to know how to us the copy command button. can anyone help ...

Pointing to correct macro path using excel custom toolbar
I have created an excel 2000 template (.xlt) containing a number o macros. When I open copies of this template on various pcs, the macro function correctly, except I cannot successfully run the macros usin the custom toolbar I created, because (I think) within the toolbar th paths to the macros are pointed to the original location on my pc. An advice on how I can resolve this would be gratefully received -- Message posted from http://www.ExcelForum.com Have you thought about building the toolbar when the file opens? Or maybe separating the worksheet portion of the template from the code pa...

excel locks up after selecting a cell #2
excel locks up after selecting a cell. When ever, I select a Cell, that will automatically selects all the cell and this freezes the entire computer. Can any body who would help me resolve this issue? Please help.... ...

Hide row code help
I am using the following script to hide or unhide rows. How do I code the offset to only work when a row is being unhid. If we cant do this only offset when it is being hidden or unhidden? Thank you Private Sub Worksheet_Change(ByVal Target As Range) 'Application.EnableEvents = False' not necessary If Not Application.Intersect(Target, Range("B9")) Is Nothing Then Rows("10:11").Hidden = IIf(UCase(Range("B9")) = "", True, False) 'spelling ActiveCell.Offset(-6, 0).Select End If 'Application.EnableEvents = False ...

Entire Visio page moves whenever I use the directional arrow keys instead of the object I've clicked on
Gurus, Running Visio 2003. For some reason lately, whenever I click on an object and try to move it using the right, left, or up or down arrow keys, the whole page moves instead of the object I've clicked on. This is really annoying! It didn't used to be this way. I'm not sure what I changed. I simply want the object I've highlighted to move whenever I use the arrow keys not the whole Visio page itself! -- Spin On Fri, 24 Oct 2008 17:29:14 -0400, "Spin" <Spin@invalid.com> wrote: >Gurus, > >Running Visio 2003. For some reason lately, whe...

.NET 3.5
Hello, Does anyone of you know how I can force vb.net to remove the handle of a local DLL which I used beforehand with Reflection. Here is a fragment of the code. I have a base class and a derived class. This app only knows the base class and creates an Instance of the derived class by using Reflection which works fine. The only problem I have is that the local DLL file is blocked (I can't delete it) as long as I don't close the Application Dim GetCustomModule as BaseClass Dim dll As Assembly = Assembly.LoadFrom("c:\tools\DerivedCass.dl...

Ex 2003 IS backup, using NTBackup 5.0
On Win2K SP4, I've upgraded from Ex 2000 ==> Ex 2003. I use NTBackup to back up the IS. The NTBackup is version 5.0. Backup proceeds normally; on differentials I can see NTBackup counting log files, etc. However, I tested a restore, and when I catalogued the *.bkf file, NTBackup says "No files were found". So I have an error-free backup, from which (apparently) I cannot restore. Is the Ex 2000 ==> Ex 2003 upgrade supposed to upgrade NTBackup as well, to a version that can handle restoring Ex 2003 IS? Does anyone have an insight into why NTBackup doesn't want to ...

Can't Select Addresses for "Send Email Statements" Function
When I try to select an email address from my Outlook address book on the "send email statements" area on the customer maintenance options window, the email does not display. I get the dialog box to select the email from my address book, but after clicking ok, the address does not display in the appropriate field (I've tried TO:, CC:, and BCC:). I'm using Windows 7 pro 64 bit and Outlook 2007. Any ideas? More info on this. Other users who are running Windows XP and Outlook 2003 can select entries from their address book and have them show up in the appropr...

Option trades not using "cash" account
I am using the trial version of Money 2006 Premium and am seeing a problem which also occured in Money 2003. When opening or closing an option position in an investment account, the "Transfer to:" field is left blank by default even though a "cash portion" is defined for that account. This is a minor annoyance in that, for each of these transactions, I need to select the appropriate cash account. In some cases, if the "Transfer to:" field is left blank, Money will ask for the cash account name but this does not always happen and the security is bought or...

Off-Topic: Newsgroup Readers/Viewers
I know this is off-topic, but I couldn't find a newsgroup that was appropriate for this post (like a lounge for MVP's or something)...so I apologize in advance. I currently use Outlook Express (or Outlook Newsreader : "C:\Program Files\Outlook Express\msimn.exe" /outnews) for viewing newsgroups. While its a nice program, it does have a few shortcomings, not the least of which is its difficulty in truly tracking and reporting responses to posts made by the user. I figured since a lot of MVP's and others on these newsgroups tend to respond to a lot of newsgroup posts, an...

Using "Avg" function on calculated field
In a report I have a calculated field as "=Nz([Total],0)/Hrs" which works fine. In the report footer I tried to get the average of the values in this column so I used "=Avg(Nz([Total],0)/Hrs) but when I run it it asks me for the value of "Hrs". Neither "hrs" nor "total" are calculated fields and no error is generated either. The output for the average is just blank. I tried =Avg(Nz([Total],0)/(Nz(Hrs,))) which did not work either. What am I missing? There's a couple of potential issues here. It sounds like Hrs is a field in the table/quer...

Increasing beyond 65,536 rows
Does anyone know how I can increase the number of rows in an Exce worksheet ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com "speedy" <speedy.xfpwa@excelforum-nospam.com> wrote... >Does anyone know how I can increase the number of rows in an Excel >worksheet? You can't. Excel provides 65536 rows unless you use Excel 5/95 or even earlier versions, in which case it provides only 16384 rows. ...

Variable Width 100% Stacked Column Chart
Hi, I need to make a variable width 100% stacked column chart (similar t the Column chart with varying width bars from andypope.info but 100 stacked) The x-axis would contain various market segments and the width would b proportional to their size, the y-axis divides each segment up by a attribute. Any ideas? Thank ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Earl - Stephen Bullen has a Variable Width Column Chart example on his web site (http://bmsltd.co.uk), lo...

How to breakthrough row number limitation
Hi all, The default row number limitation for each sheet is 65,536. Is it possible to increase it up to 100,000 ? Your help would be appreciated. Billy No. You'll have to use multiple columns or multiple worksheets (or another app, like Quattro Pro or Access). In article <OcGz2DVHFHA.3612@TK2MSFTNGP09.phx.gbl>, "Billy Leung" <billykyleung@dont.send.me.mail.com> wrote: > > The default row number limitation for each sheet is 65,536. Is it possible > to increase it up to 100,000 ? Your help would be appreciated. Hi JE McGimpsey, thank you so much for...