VBA to dynamically filter an excel column

Question

I am trying to write a macro to for a rule.

A   B   C   D   E

X   1   RT  YY  SOW  
D   3   FT  GH  TOW  
F   4   FG  TY

Rule: Filter on column E one by one and copy the value in column A and C. The value in column E is not predetermined. So I can have an excel with different values in column E and hence I cannot hardcode my filter criteria.

I want to filter on first value (sow in example) then whatever rows I get, I copy column E value in column A and C for those rows. Then I filter on second value (tow in example) and copy it in column A and C.

The result should look like this

A     B    C   D   E

SOW   1   SOW  YY  SOW  
TOW   3   TOW  GH  TOW  
F     4   FG   TY

Note: Now of course there will be hundreds of rows and just not one row. and hence the need for a macro.

Clarification : I want to take everything but blanks, from column E and copy into respective rows in column A and C. But since column E does not have strict defined values, I am not able to hard code my filter criteria. Column E may have different number of unique values and combination. And I would like to take the values as is without the blanks and copy into columns A and C.

After copying columns A & C .. Where would you like to paste your data? — YasserKhalil
– YasserKhalil, Commented Oct 30, 2018 at 12:57
Sill not clear. Can you provide snapshot with before and after? — YasserKhalil
– YasserKhalil, Commented Oct 30, 2018 at 13:00
It feels like you just can copy column E and replace it with column A and C. Doesn't need to filter to do that.... or what I'm missing? And what do you want to do when there are blank cells, like the last row in column E? — Wizhi
– Wizhi, Commented Oct 30, 2018 at 13:01
@Wizhi- has a point, why would you need to filter? just copy column E and paste to column A & C — Davesexcel
– Davesexcel, Commented Oct 30, 2018 at 13:06

Wizhi · Accepted Answer · 2018-10-30 13:32:35Z

2

If I understand your question correctly you only want to copy the values in Column E that is not empty. Therefore you want to filter it.

VBA Code:

Sub CopyNoneEmptyCells()

Dim ws As Worksheet
Set ws = ActiveWorkbook.Worksheets("Sheet1") 'Define your worksheet name
Dim lrow As Long
Dim i As Long


lrow = ws.Cells(Rows.Count, 1).End(xlUp).Row 'Check last row in column A 'Find the last row in column A

For i = 2 To lrow 'Loop from row 2 until last row. I assume you have a header at row 1
    If ws.Cells(i, 5).Value <> "" Then 'If cell is not empty in column E, then
         ws.Cells(i, 1).Value =  ws.Cells(i, 5).Value 'Copy value from column E to Column A
         ws.Cells(i, 3).Value =  ws.Cells(i, 5).Value 'Copy value from column E to Column C
    End If
Next i 'Next row
End Sub

edited Oct 30, 2018 at 13:32

answered Oct 30, 2018 at 13:19

Wizhi

6,5894 gold badges29 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Kunal Puranik Over a year ago

yeah this is exactly what I was trying to do, but instead of a loop I was trying to do it through filtering. But this works too. Thanks

Wizhi Over a year ago

Both methods works fine (loop or filter) :), good you provided a result table!. That gave a lot of valuable information to us. Good luck with your project and welcome to SO :)

Kunal Puranik Over a year ago

got it. :) Thanks for the help Hoping to learn a lot here :)

Wizhi Over a year ago

Almost... In the example, the last row has an empty cell in column E. Therefore if you copy that value it would overwrite valuable information in Column A and C (they would be blank). We only want to copy a cell value from column E if the current cell ws.Cells(i, 5).Value <> "" is non empty. Otherwise go to next row.

Collectives™ on Stack Overflow

VBA to dynamically filter an excel column

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related