• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Code for filtering data

B

Brijesh

Member
Hi All
I am working on a excel sheet in which data is appearing in columns B, C and D from row 8 to 81 with heading in row 7. Data is filtered in ascending order in column B (all column contains text data). Now I have to enter a text data in row 82 column B (i.e. cell B82). I want that as soon as I enter a data in row 82 column B, a macro should be triggered which should arrange the data in column B in ascending order again. This process should be repeated every time when i enter data in column B in a new row. Please tell me what code should i use to trigger macro and also what should be the coding of the macro which would filter the data. Further after doing this I had to color the rows with alternate color i.e. row 8, 10, 12.... should be colored with a specific color and row 9, 11 ,13... should be colored with other specific color....
Seeking for the help...
 
I am confused :S

You say filter but the description implies you are trying to sort. See if below helps you.

1. Right click on the Sheet's tab which will open menu.
2. Select "View Code". It will open Visual Basic Editor window.
3. Paste the following code in the coding pane and test.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lngLR As Long
'\\ We don't want many events to trigger as there will be many cells
'\\ If Error comes then it might leave us with disabled events so
'\\ reset application defaults
On Error GoTo EOSub
With Application
  .EnableEvents = False
  .ScreenUpdating = False
End With

'\\ Check if there's enough data for sort or column or row is of our interest
lngLR = Range("B" & Rows.Count).End(xlUp).Row
If lngLR < 7 Or Target.Column <> 2 Or Target.Row < 8 Then GoTo EOSub

'\\ Actual sort code
Range("B7:D" & lngLR).Sort Key1:=[B8], Order1:=xlAscending, Header:=xlYes

'\\ Reset defaults
EOSub:
With Application
  .EnableEvents = True
  .ScreenUpdating = True
End With
End Sub
 
@shrivallabha : I think the worksheet_change module will impact any change in the worksheet, however the OP is asking for a only a data change in Col.B..
(Just came in my mind while reading out your code)

@Hui @SirJB7 :
I can't see my earlier comment on this,was this deleted?... :confused:
 
Abhijeet R. Joshi said:
@shrivallabha : No I dint get a chance to try it out, just thought of this while reading, I might be wrong as well....:)
Click to expand...
Following portion takes care of your concerns:
Code: 
'\\ Check if there's enough data for sort or column or row is of our interest
lngLR = Range("B" & Rows.Count).End(xlUp).Row
If lngLR < 7 Or Target.Column <> 2 Or Target.Row < 8 Then GoTo EOSub
1. If there's no data to sort in column B
2. If the cell changed is not in column B.
3. If the cell changed does not belong to data rows which start at 8

If any of these conditions are met then the code skips sorting part and jumps to flag EOSub which resets application defaults which we have altered at the beginning of the code.

In hindsight, I'd drop the first check. The following check will see if the range falls in Column B and is a data row which is sufficient.
Code: 
If Target.Column <> 2 Or Target.Row < 8 Then GoTo EOSub

There are times when I do not understand the code posted by only reading so I just copy and paste them and try running them. I'd suggest you do the same thing whenever possible.

And a warning :) don't get into betting with SirJB7 as you must have seen his movies he has this natural knack of turning the odds into his favor.
 
You must log in or register to reply here.
Back
Top