Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Hide / Show Rows based on Conditions

  Asked By: Laaibah    Date: Nov 14    Category: MS Office    Views: 1323
  

I would appreciate your help with the following.

I have a spreadsheet with data in columns A to T.

I need a VBA code for the following conditions ...

Conditions for Macro:

1. Only display complete row, if cell has "F" in it . "F" is on its
own not as part of a word.

2. Where there is a letter "F", replace in final spreadsheet once
macro has run, with the word "Alert!"

3. If there is no "F" in the row, and it there is the
word "Passport" in column L, HIDE LINE/ ROW

4. If there is no "F" in the row, and it does not have the
word "Passport" in column L, SHOW line/ row

5. Sort the whole result, by column "B"

Share: 

 

22 Answers Found

 
Answer #1    Answered By: Clayton Richardson     Answered On: Nov 14

I would really appreciate a response to this.Even if you need further
clarification.

With regards to the original email I wish to add that :
Condition 1 and 2 are linked .... Once the first condition has run  then replace
"F" with "Alert".condition 3 seems surplus since line would be hidden
anyway.But for condition 4 I would need to show  row if it has neither "F" nor
"passport" in column L. “Passport” has to be in column L.I hope this is
clearer.

 
Answer #2    Answered By: Adelinda Fischer     Answered On: Nov 14

Yes, a bit more clarification.
for 1. What cell  do you want to put "F" in? A specific column or anywhere
column A-T.
2. "Final Spreadsheet" ?? Are you copying to another sheet or are you talking
about this one?
3. Once #2 is complete, there is no "F" in the row! so shouldn't you test if
there is not "Alert"?
4. sort: easy...
So.. is this interactive? Meaning, are you making changes and wish for it to
update, or are
you loading from another source?

 
Answer #3    Answered By: Tamara Nguyen     Answered On: Nov 14

1- "F" would already be there ...spread around the spreadsheet.

2- I will not copy to another sheet but just this one.
This will allow the user to see what they want "quickly" and take action as
necessary.

3 - Once finished the filtered portion would show  "F" as "Alert SO the HR
department know that they should be doing something.

4 - HR will be updating a similar spreadsheet  each month and then running the
macro to check for action points.

 
Answer #4    Answered By: Ujala Hashmi     Answered On: Nov 14

Also, instead of hiding the rows, why not use Filters?
Is there any reason that you prefer to hide  them?

 
Answer #5    Answered By: Robin Bailey     Answered On: Nov 14

Its not for my use ... its for someone else.

More automation the better.

 
Answer #6    Answered By: Oscar Evans     Answered On: Nov 14

OK, here's what I came up with:
Put this into a module:
Sub Update()
Dim Data_Rowcnt, I, HideCnt
HideCnt = 0
Application.ScreenUpdating = False
Data_Rowcnt = Application.WorksheetFunction.CountA(Range("A1:A65500"))
Cells.EntireRow.Hidden = False
Application.StatusBar = "Processing " & Data_Rowcnt & " Records"
For I = 1 To Data_Rowcnt
If (I Mod (Round(Data_Rowcnt / 20, 0)) = 0) Then
Application.StatusBar = I & " of " & Data_Rowcnt & " = " & Round((I /
Data_Rowcnt) * 100, 0) & "%"
If (UCase(Cells(I, 1)) = "F") Then
Cells(I, 1) = "Alert"
Else
If (UCase(Cells(I, 12)) = "PASSPORT") Then
HideCnt = HideCnt + 1
Rows(I).EntireRow.Hidden = True
End If
End If
Next I
Cells.Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, _
Order2:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Application.ScreenUpdating = True
MsgBox HideCnt & " rows  hidden"
Application.StatusBar = False
End Sub

 
Answer #7    Answered By: Michael Evans     Answered On: Nov 14

Thanks

I will try this .....................

 
Answer #8    Answered By: Woodrow Jones     Answered On: Nov 14

OK.. I'll have to make a modification.
this ONLY looks for "F" in column "A"...
but you said that it could be anywhere...
Any cell  that contains only "F" should be changed...
I'll make the changes.
Try this one:
Option Explicit
Sub Update()
Dim Data_Rowcnt, I, C, HideCnt, AlertFlag
HideCnt = 0
Application.ScreenUpdating = False
Data_Rowcnt = Application.WorksheetFunction.CountA(Range("A1: A65500"))
Cells.EntireRow.Hidden = False
Application.StatusBar = "Processing " & Data_Rowcnt & " Records"
For I = 1 To Data_Rowcnt
If (I Mod (Round(Data_Rowcnt / 20, 0)) = 0) Then
Application.StatusBar = I & " of " & Data_Rowcnt & " = " & Round((I /
Data_Rowcnt) * 100, 0) & "%"
AlertFlag = False
For C = 1 To 20 'Look in columns  A to T
If (UCase(Cells(I, C)) = "F") Then
Cells(I, 1) = "Alert"
AlertFlag = True
Exit For
End If
Next C
If (UCase(Cells(I, 12)) = "PASSPORT") And (Not AlertFlag) Then
HideCnt = HideCnt + 1
Rows(I).EntireRow.Hidden = True
End If
Next I
Cells.Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, _
Order2:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Application.ScreenUpdating = True
MsgBox HideCnt & " rows  hidden"
Application.StatusBar = False
End Sub
BEWARE: It DOES "assume" that there ALWAYS is data  in column "A".
If this is not a good assumption, are there other columns that
ALWAYS have data?

 
Answer #9    Answered By: Iris Sanders     Answered On: Nov 14

The first set of code  hid everything but with the modified code I believe we are
getting there.

The code displays and hides lines as per the requirements.

Now how do I convert "F" to "Alert" ? This step will happen after the modified
code has been ran.

Also code to sort by column "B".

 
Answer #10    Answered By: Olga Allen     Answered On: Nov 14

Sorry, when I modified the code  to search all of the cells in the row,
I forgot to update the line:
Cells(I, 1) = "Alert"

should read: Cells(I, C) = "Alert"

the code already has the sort after the changes have been made.

 
Answer #11    Answered By: Botan Suzuki     Answered On: Nov 14

It works except for the fact that it only converts the first "F" in a row  to
"Alert". On some rows  there are three to four "F" 's on a row.

 
Answer #12    Answered By: Fahmida Ahmed     Answered On: Nov 14

After the line:

AlertFlag = True
remove the line:
Exit For

(this is a loop from column "A" to Column "T",
when a cell  with the letter "F" is found, it
changes the value to "Alert". The "Exit For"
statement breaks out of the loop. you want
to continue for all cells in the row.)

 
Answer #13    Answered By: Vidhya Iyer     Answered On: Nov 14

That seems to have done the trick.

Many Thanks for your help.

 
Answer #14    Answered By: Cyka Jansen     Answered On: Nov 14

I need to slightly change the code  you provided below to work along all sheets
except the first one. The name for the first sheet would be "Macro". This would
contain a macro  button which ther user would "click" to carry out the required
filter.

The original code was ...

Option Explicit
Sub Updates2()
Dim Data_Rowcnt, I, C, HideCnt, AlertFlag
HideCnt = 0
Application.ScreenUpdating = False
Data_Rowcnt = Application.WorksheetFunction.CountA(Range("A1: A65500"))
Cells.EntireRow.Hidden = False
Application.StatusBar = "Processing " & Data_Rowcnt & " Records"
For I = 1 To Data_Rowcnt
If (I Mod (Round(Data_Rowcnt / 20, 0)) = 0) Then Application.StatusBar =
I & " of " & Data_Rowcnt & " = " & Round((I / Data_Rowcnt) * 100, 0) & "%"
AlertFlag = False
For C = 1 To 20 'Look in columns  A to T
If (UCase(Cells(I, C)) = "F") Then
Cells(I, C) = "Alert"
AlertFlag = True
End If
Next C
If (UCase(Cells(I, 12)) = "PASSPORT") And (Not AlertFlag) Then
HideCnt = HideCnt + 1
Rows(I).EntireRow.Hidden = True
End If
Next I
Cells.Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, _
Order2:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A1").Select
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub

I have tried to tweak it and assign to macro button but it does not work across
all worksheets.

 
Answer #15    Answered By: Robin Hayes     Answered On: Nov 14

There's a couple of ways to do it.
First, let me slip into "teacher" mode:

How do you WANT to do it?
What steps would you go through to do it manually?
This is the logic you use to help  decide what to do.
So...
You WANT to make this work with each sheet...
You could select each sheet, then run  this macro.
Which means you could add a sub like:

sub Update_All()
for I = 1 to sheets.count
if (Ucase(sheets(I).name) <> "MACRO") then
sheets(I).select
Updates2
end if
Next I
End Sub

Now, if you have a LOT of sheets, we may want to look at
turning off screen updating in this sub and not in the other.

give it a shot and let me know how it works.

 
Answer #16    Answered By: Ibtihaj Akhtar     Answered On: Nov 14

Thanks I will try it ... my aim is to make it simple for the end user i.e. all
they have to do is click on the macro  button.

I will get back to you ...in a bit.

 
Answer #17    Answered By: Leonardo Costa     Answered On: Nov 14

This works but I would like to incorporate code  which automatically selects all
sheets when the "macro button" activated. The number of sheets can vary month by
month.Once selected then the "update macros" would also run.

 
Answer #18    Answered By: Camille Garrett     Answered On: Nov 14

This would handle any new sheets.
It works on ANY sheet not called "Macro".
So if there is two sheets or two hundred (which I think is doubtful)
it would cycle through each one.

(for I = 1 to sheets.count)

 
Answer #19    Answered By: Olivia Campbell     Answered On: Nov 14

How would I amend the code  which you gave earlier to "delete" the rows  rather
than "hide" the rows. Alternatively to add code to delete hidden lines.

Please see below :

Option Explicit
Sub Updates2()
Dim Data_Rowcnt, I, C, HideCnt, AlertFlag
HideCnt = 0
Application.ScreenUpdating = False
Data_Rowcnt = Application.WorksheetFunction.CountA(Range("A1: A65500"))
Cells.EntireRow.Hidden = False
Application.StatusBar = "Processing " & Data_Rowcnt & " Records"
For I = 1 To Data_Rowcnt
If (I Mod (Round(Data_Rowcnt / 20, 0)) = 0) Then Application.StatusBar =
I & " of " & Data_Rowcnt & " = " & Round((I / Data_Rowcnt) * 100, 0) & "%"
AlertFlag = False
For C = 1 To 26 'Look in columns  A to Z
If (UCase(Cells(I, C)) = "F") Then
Cells(I, C) = "Awaiting"
AlertFlag = True
End If
Next C
If (UCase(Cells(I, 12)) = "PASSPORT") And (Not AlertFlag) Or
(UCase(Cells(I, 13)) = "PASSPORT") And (Not AlertFlag) Then
HideCnt = HideCnt + 1
Rows(I).EntireRow.Hidden = True
End If
Next I
Cells.Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, _
Order2:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A1").Select

Application.ScreenUpdating = True
Application.StatusBar = False
End Sub

 
Answer #20    Answered By: Hariz Burki     Answered On: Nov 14

deleting rows  is easy.
to find the syntax, simply record a macro  and delete a row.
Then look at the macro.
It will say something like:

Rows("45:45").Select
Selection.Delete Shift:=xlUp

You can combine that to:

Rows("45:45").Delete Shift:=xlUp

Now, to make it fit your macro,
comment out the line:

Rows(I).EntireRow.Hidden = True
and add the line:

Rows(I).Delete Shift:=xlUp



Now, here's the tricky part.
You're searching from line #1 to the last line...
Say, you get to line 10 and decide to delete it.
10 is gone, line 11 is now 10.
but the next iteration of the loop moves to line 11,
which means that the current line 10 is never processed!

To get around that, we need to reverse the direction of the loop.

Change the line:

For I = 1 to Data_Rowcnt
to:
For I= Data_Rowcnt to 1 step -1

VBA is kind-of funny... you'd think that if you said to go from 10 to 1,
VBA would figure out that it would have to go BACKWARDS, but no..
instead it will start at 10 and go merrily along until it reaches 1..
which is ALMOST never.
If you don't Declare "I" as an Integer or Long data  type,
it defaults to "Variant". I think once you set it to Data_Rowcnt, then
it redefines itself as Integer, which means it's limited to 32,767.
But once beyond that, it switches to Long, so then it can go up to
2,147,483,647.
Then, if you have On Error Resume Next, it's possible it will reset to 0,
and your next loop is 1!!!
If not, then once it reaches 2,147,483,647 then it will kick out an error...
so, it's best to just TELL it what to use as an increment.

BTW.. this is also useful if you're dealing with blocks of data.
Say 4 lines to a record? Like:
Name
Address
City,State,Zip
Phone
then you can use:
For I = 1 to Rowcnt step 5
TName = Cells(I,1)
TAddr = Cells(I+1,1)
TCity = Cells(I+2,1)
TPhon = Cells(I+3,1)
(do something fun here)
Next I

 
Answer #21    Answered By: Bien Nguyen     Answered On: Nov 14

Thanks again for your help  and explanation.

I admit when I initially tried on my own not all the lines were being deleted.
This was prior to emailing the group.

Would it be possible to put expalnations against each line of code  from the
finished code below. It is a vey useful piece of code and I am sure other
members would also find it helpful.

Sorry for any inconvenience.

I am especially interested in these lines ...

If (I Mod (Round(Data_Rowcnt / 20, 0)) = 0) Then Application.StatusBar = I & "
of " & Data_Rowcnt & " = " & Round((I / Data_Rowcnt) * 100, 0) & "%"
AlertFlag = False


My final  code ...

Option Explicit
Sub Updates2()
Dim Data_Rowcnt, I, C, HideCnt, AlertFlag
HideCnt = 0
Application.ScreenUpdating = False
Data_Rowcnt = Application.WorksheetFunction.CountA(Range("A1: A65500"))
Cells.EntireRow.Hidden = False
Application.StatusBar = "Processing " & Data_Rowcnt & " Records"
For I = Data_Rowcnt To 1 Step -1
If (I Mod (Round(Data_Rowcnt / 20, 0)) = 0) Then Application.StatusBar =
I & " of " & Data_Rowcnt & " = " & Round((I / Data_Rowcnt) * 100, 0) & "%"
AlertFlag = False
For C = 1 To 26 'Look in columns  A to Z
If (UCase(Cells(I, C)) = "F") Then
Cells(I, C) = "Awaiting"
AlertFlag = True
End If
Next C
If (UCase(Cells(I, 12)) = "PASSPORT") And (Not AlertFlag) Or
(UCase(Cells(I, 13)) = "PASSPORT") And (Not AlertFlag) Then
HideCnt = HideCnt + 1
Rows(I).Delete Shift:=xlUp
End If

Next I
Cells.Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, _
Order2:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A1").Select
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub

 
Answer #22    Answered By: Freddie Evans     Answered On: Nov 14

Sure... Here is a probably long-winded explanation for each line:

Option Explicit ' This says that each variable must be EXPLICITly defined.
' Which means that there must be a Dim,Public,Global, etc
' Statement for each variable used.
' It has saved me literally HUNDREDS of hours in debugging
' time. Missing or transposed letters in variable names
' can be very hard to find without it.

Sub Updates2() ' Self explanatory
'
Dim Data_Rowcnt, I, C ' Declaration of Local variables

Dim HideCnt, AlertFlag ' Remember, variables declared within a Sub/Function
' Are only available to THAT Sub or Function.
' That enables you to use a loop counter like "I"
' in one Sub, and if within this sub you call another
' Sub or Function, you can still use "I" there
' without losing the original value.

HideCnt = 0 ' The original script HID the records rather than deleting them.
' For Status reporting, I like to keep track of the number of
records
' Being affected.

Application.ScreenUpdating = False 'If you like seeing your spreadsheet
flash and
'dance while your script runs, just so
that you
'know it's still doing something, then by
all
'means, comment out this line, but all
the
'screen updating eats up CPU cycles
(makes the
'program run  longer). I like it to be
fast
'without all the pretty lights.

Data_Rowcnt = Application.WorksheetFunction.CountA(Range("A1: A65500"))
' Most (if not all) "regular" Excel worksheet functions are
available to
' VBA. In Excel, if I wanted to know the number of non-blank cells
in a
' column, I would use =COUNTA(A:A). Instead of putting it in a
cell,
' I used the vba  equivalent... ("Application" is Excel)

Cells.EntireRow.Hidden = False 'This "unhides" all rows... Not really
necessary
'Any longer, since we changed the Sub to
delete
'the rows  instead of hiding them.

Application.StatusBar = "Processing " & Data_Rowcnt & " Records"
'This puts the Status bar to work for you. Instead of it saying things
like:
'"Ready", or "please wait..", I like to have it show  what the macro/sub is
'actually DOING. Here, it gives the number or records about to be
processed.

For I = Data_Rowcnt To 1 Step -1 'Since we're deleting rows, the "row count"
is
'constantly changing. Let's say that you have 100 rows. When you get
to
'row 10, the test indicates that you want to delete the row.
'Once deleted, row  11 moves to row 10.
'In the next iteration of the loop, row 11 is tested, but that means
that
'the current row 10 is skipped! Therefore, I START the loop at the end
'of the list (Data_Rowcnt) and work backwards to row 1, one row at a
time
'(step -1).

If (I Mod (Round(Data_Rowcnt / 20, 0)) = 0) Then _
Application.StatusBar = I & " of " & Data_Rowcnt & " = " _
& Round((I / Data_Rowcnt) * 100, 0) & "%"
'As I mentioned before, I like to see what's going on by looking
'at the Status bar. My goal here is to see something like:
' 561 of 11220 = 5%
'Now, if you're processing a LOT of records, displaying updates
'for every record uses a LOT of CPU time, but only showing the
'status every 10% may be too long to provide adequate feedback.
'So, in this case I wanted to show the status every 5%.
'Now, how many records are 5% of the total?
'That would be the total records / 20. So, when the loop counter
'reaches a multiple of the 5% number, you want to update the
'Status bar. But, let's say the record count is 11,229, then the
'5% count is 561.45. The loop counter will never match this
because
'it is a whole number. So, I rounded it to "0" places.
' Round(Data_Rowcnt / 20, 0)
' Next is the "mod" function. Mod gives you the REMAINDER of a
'division operation. For instance, 10/2 is 5, with a remainder of
0.
'so, 5 mod 10 = 0. 10/3 = 3, with a remainer of 1, so 3 mod 10 =
0.
'Now, using the 5% number, I check to see if the loop counter
divided
'by the 5% number has a 0 remainder.. which means it has completed
'the NEXT 5%..
' I then have to come up with a message that is meaningful.
' For that, I calculate the % complete  I/Data_Rowcnt * 100.
' Seems like a lot of work for just a display, but I have to make
'it look like I'm doing SOMETHING!

AlertFlag = False 'Used to indicate if a line should be deleted

For C = 1 To 26 'Cycle through columns  A to Z
If (UCase(Cells(I, C)) = "F") Then 'Check for a value of "F"
Cells(I, C) = "Awaiting" 'Change the "F" to "Awaiting"
AlertFlag = True
End If
Next C

' I think this next statement needs some work.
' It seems to be missing some "associations"
'It looks like you want to delete a line if
'There was no "F", and "passport" is in column 12 or 13.

' If (UCase(Cells(I, 12)) = "PASSPORT") And (Not AlertFlag) _
' Or (UCase(Cells(I, 13)) = "PASSPORT") And (Not AlertFlag) Then
' I would change it to:
If ((Not AlertFlag) _
And ((UCase(Cells(I, 12)) = "PASSPORT") _
Or (UCase(Cells(I, 13)) = "PASSPORT"))) Then
HideCnt = HideCnt + 1 'Increment record counter
Rows(I).Delete Shift:=xlUp 'Delete the row
End If

Next I 'Proceed to next line
Cells.Select 'Select all remaining cells

'I think this sort needs some work.
'It looks like it was recorded from a result set with only one record.
'then modified. I don't think there should be an Order2 without a Key2.
'You might want to re-record this with a larger data  set, perhaps even
'all of the records so that the range will be much larger than anticipated.
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, _
Order2:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Range("A1").Select 'Set cursor to a known position (un-select any
selected cells)

Application.ScreenUpdating = True 'Once complete, display  all of the
changes you've made.

'The Status bar will still display the last updated message until you exit
excel.
'since you probably want to see other message, release the status bar so
Excel can use it.
Application.StatusBar = False
End Sub

We aren't doing anything with the HideCnt variable.
Before exiting, you could display the number of records using something like:
Msgbox HideCnt & " Records to Correct"

 
Didn't find what you were looking for? Find more on Hide / Show Rows based on Conditions Or get search suggestion and latest updates.




Tagged: