CODEWITHSUNDEEP
excelvba
Kunal Puranik
Kunal Puranik

Reputation: 35

VBA to dynamically filter an excel column

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.

Upvotes: 0

Views: 387

Answers (1)

Wizhi
Wizhi

Reputation: 6549

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

Upvotes: 2

Related Questions