Excel2000 VBA If-Then/Loop Failures

  • Thread starter Thread starter prodsched
  • Start date Start date
P

prodsched

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

(e-mail address removed)
 
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
 
LOL - Good Stuff - That _ simply denotes a continuation onto the next line for
that statement.
 
Back
Top