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

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

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

Excel2000 VBA If-Then/Loop Failures
 

Hmmm - Works fine for me in 2000. What line does it error on? It didn't suffer
from wordwrap anywhere did it, such that you may see red lines of data in the
code when you pasted it in. Happy to send you a copy that is working on my
machine, and then if that doesn't work will have to think again as to what is
causing it. If you want me to send you that then email me taking the NOSPAM bit
out of my email address



--
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
...
Thank you so much for your quick response.

I have tried both routines and both cause and error message as
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 error
message.

Apparently I am still missing something here. Thanks again...


---
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

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

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