Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel2000 VBA If-Then/Loop Failures
Excel Spreadsheet with fixed number of columns (44) and varying numbe
of rows. The data of interest starts at ROW 9. After worksheet is updated by Shipping (Column Q), Object of macro i to hide rows that have shipped. Look at each ROW in column "Q". Looking for word "Shipped". If found "Shipped" then stop, select the entire ROW and hide it. The GOTO next row. Repeat. IF not found Then go to next row. Repeat. Continue to end of rows. End I have tried LOOPS, FOR NEXT, IF THEN and can't seem to find combination that works! What am I missing??? HELP -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel2000 VBA If-Then/Loop Failures
This will do it, but it's just as easy to use Data / Filter / Autofilter. The
code will prompt you for a value, so if you don't want it to do that, use the second variant:- Sub HideRows() ans = InputBox("What string do you want rows to be Hidden if they contain it?") Application.ScreenUpdating = False LastRow = ActiveSheet.UsedRange.Row - 1 + _ ActiveSheet.UsedRange.Rows.Count Set Rng = Range(Cells(1, "Q"), Cells(LastRow, "Q")) With Rng .AutoFilter .AutoFilter Field:=1, Criteria1:="<" & ans End With Application.ScreenUpdating = True End Sub '------------------------------------------- Sub HideRows() Application.ScreenUpdating = False LastRow = ActiveSheet.UsedRange.Row - 1 + _ ActiveSheet.UsedRange.Rows.Count Set Rng = Range(Cells(1, "Q"), Cells(LastRow, "Q")) With Rng .AutoFilter .AutoFilter Field:=1, Criteria1:="<Shipped" End With Application.ScreenUpdating = True End Sub -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "prodsched " wrote in message ... Excel Spreadsheet with fixed number of columns (44) and varying number of rows. The data of interest starts at ROW 9. After worksheet is updated by Shipping (Column Q), Object of macro is to hide rows that have shipped. Look at each ROW in column "Q". Looking for word "Shipped". If found "Shipped" then stop, select the entire ROW and hide it. Then GOTO next row. Repeat. IF not found Then go to next row. Repeat. Continue to end of rows. End I have tried LOOPS, FOR NEXT, IF THEN and can't seem to find a combination that works! What am I missing??? HELP! --- Message posted from http://www.ExcelForum.com/ --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.605 / Virus Database: 385 - Release Date: 01/03/2004 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel2000 VBA If-Then/Loop Failures
Thank you so much for your quick response.
I have tried both routines and both cause and error message a follows. Runtime Error 438 Object doesn't support this property or method. I have entered values for LastRow=ActiveSheet.UsedRange.Row-1+_ I have used positive and negative integers and keep getting the erro message. Apparently I am still missing something here. Thanks again.. -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel2000 VBA If-Then/Loop Failures
Hello Ken:
Thank you for your patience with me. I have discovered the problem. The third line of code reads, "LastRow=Active.Sheet.UsedRange.Row-1+_" Duh....Where the _ is I had a number there instead! Why, I don't know. In any case I have replaced the number with _ and all works fine now Thanks again so very much. Best regards -- Message posted from http://www.ExcelForum.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel2000 VBA If-Then/Loop Failures
LOL - Good Stuff - That _ simply denotes a continuation onto the next line for
that statement. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "prodsched " wrote in message ... Hello Ken: Thank you for your patience with me. I have discovered the problem. The third line of code reads, "LastRow=Active.Sheet.UsedRange.Row-1+_" Duh....Where the _ is I had a number there instead! Why, I don't know. In any case I have replaced the number with _ and all works fine now! Thanks again so very much. Best regards, --- Message posted from http://www.ExcelForum.com/ --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.608 / Virus Database: 388 - Release Date: 03/03/2004 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
workbook link failures | Links and Linking in Excel | |||
Excel 2002 : Need patching for formula failures | Excel Discussion (Misc queries) | |||
Excel2000 | Excel Programming | |||
Excel2000 Upgrades | Excel Programming | |||
HELP!!!! Can't stop a loop (NOT an infinite loop) | Excel Programming |