Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

copy item and ignore empty cells

0

Hello

I try writing simple code to my friend by copy item (MMMA) from column A:B  to column D:E by ignore duplicates items and empty cell after show items in column D:E .

my proplem will show  empty cells in column D:E 

I posted the right result in columns D:E 

this is the code 

Sub copy_items()
Dim lr As Long
Dim i As Long
lr = Worksheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lr
With Worksheets("sheet1")

If Not IsEmpty(.Cells(i, 1)) And Not IsEmpty(.Cells(i, 2)) And .Cells(i, 1) = "MMMA" Then
.Cells(i, 4) = .Cells(i, 1): Cells(i, 5) = .Cells(i, 2)
End If
End With
Next
With Worksheets("sheet1")
        .Range("$D:$E").RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
End With
End Sub

thanks

excel
MS Office Version: 2010
posted by: leopard (rep: 88) on Sat Feb 3, 2024 at 12:34 pm
Answer
Discuss

Answers

0
Selected Answer

Hi again Leopard.

You could try the approach in the file attached.

The code is just:

Sub AdvFilter_items()

    With Worksheets("sheet1")
        If .FilterMode = True Then .ShowAllData
        .Range("G:H").Clear
        .Range("A:B").AdvancedFilter _
            Action:=xlFilterCopy, _
            CriteriaRange:=.Range("D2:E3"), _
            CopyToRange:=.Range("G1")
        ' remove duplicates and blanks in Outcome column of copied range
        With .Range("G:H")
            .RemoveDuplicates Columns:=2
            .SpecialCells(xlCellTypeBlanks).Delete
        End With
    End With

End Sub

where all the hard work is done by the line in bold. That works in conjunction with the filter table (range) in D2:E3 which has the same headers as the data to be filtered. In D3, the formula is

="=MMMA"

so that adds a filter of NAME=MMMA. In a similar way, E2:E3 add the filter  OUTCOME<>"" so the data is filtered for NAME=MMMA  and OUTCOME<>"".

Click the (renamed) button and G:H will show the data without duplcates.

This approach is very flexible e.g. if you change D3 to:

="=*M*"

then you'd get all the names including M somewhere. ="=M*" would give any records starting with M etc.

You could move that filter table (range) to another (hidden) sheet.

Hope this helps.

posted by: John_Ru (rep: 6142) on Sat Feb 3, 2024 at 6:47 pm
Discuss

Discussion

thanks John for  your suggestion .
leopard (rep: 88) Feb 4, '24 at 12:18 pm
Happy that helped. Thanks for selecting my Answer, Leopard. 
John_Ru (rep: 6142) Feb 4, '24 at 2:29 pm
Add to Discussion
0

Hello Leopard,

Here is another way to do waht you want.

At the top of your macro you need to add the variable "lr2"

Dim lr As Long, lr2 As Long

Then, after your line if code:

Range("$D:$E").RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes

add the following:

lr2 = Worksheets("sheet1").Range("D" & Rows.Count).End(xlUp).Row
Range(Cells(2, 4), Cells(lr2, 4)). _
SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
Range(Cells(2, 5), Cells(lr2, 5)). _
SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp

In the modified attached file there is also a macro to put boarders around the items in D:E

' put boarders around cells in "D" & "E"
Call Boarder1

Whichever answer (mine or John's) solves your issue, please mark as Selected.

Cheers   :-)

posted by: WillieD24 (rep: 557) on Sat Feb 3, 2024 at 9:00 pm
Discuss

Discussion

thanks willie for your solution.
leopard (rep: 88) Feb 4, '24 at 12:18 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login