Returning values listed under multiple conditions

Hello,

I have two problems:

1) Can someone help me modify the code below so that it pulls data from columns other than column A on sheet1?

2) Can someone help me modify the code below so that the heading in column A sheet3 does not clear?

I’m working on a code that will filter through columns H & I in on sheet1 and list the values from column H that are listed multiple times under different conditions. Sheet1 was used to test the code on text in the same sheet. Sheet3 is used to test the code on an active and inactive sheet. Simply, I was trying to get the return values to appear on a different sheet and it works. But another issue occurred, I cannot figure out how to clear the content in column A of sheet3 without clearing the heading.

Sub CommandButton1_Click()
    
    Dim Data As Variant
    Dim Dict As Object
    Dim Key As String
    Dim MyList() As Variant
    Dim n As Long
    Dim Rng As Range
    Dim Wks As Worksheet
        
        Set Wks = ActiveSheet
        
        Set Rng = Worksheets("Sheet1").Range("A1").CurrentRegion
        Wks.Columns(1).ClearContents
        
        Data = Rng.Resize(ColumnSize:=2).Value
    
        Set Dict = CreateObject("Scripting.Dictionary")
        
            For i = 1 To UBound(Data, 1)
                Key = Trim(Data(i, 1))
                If Key <> "" Then
                    If Not Dict.Exists(Key) Then
                        Dict.Add Key, Data(i, 2)
                    End If
                    If Dict(Key) <> Data(i, 2) Then
                        ReDim Preserve MyList(n)
                        MyList(n) = Key
                        n = n + 1
                    End If
                End If
            Next i
            
        If Dict.Count > 0 Then
            Wks.Range("A2").Resize(UBound(MyList, 1) + 1).Value = Application.Transpose(MyList)
        End If
            
End Sub

hi,

With Wks
.Range(.Cells(2, 1), .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, 1)).ClearContents
End With


or

With Wks
.Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row).ClearContents
End With

--
isabelle



Le 2012-07-20 13:15, Tee51 a écrit :
> Hello,
>
> I have two problems:
>
> 1) Can someone help me modify the code below so that it pulls data from
> columns other than column A on sheet1?
>
> 2) Can someone help me modify the code below so that the heading in
> column A sheet3 does not clear?
>
> I’m working on a code that will filter through columns H& I in on
> sheet1 and list the values from column H that are listed multiple times
> under different conditions. Sheet1 was used to test the code on text in
> the same sheet. Sheet3 is used to test the code on an active and
> inactive sheet. Simply, I was trying to get the return values to appear
> on a different sheet and it works. But another issue occurred, I cannot
> figure out how to clear the content in column A of sheet3 without
> clearing the heading.
>
>
> Code:
> --------------------
>
> Sub CommandButton1_Click()
>
> Dim Data As Variant
> Dim Dict As Object
> Dim Key As String
> Dim MyList() As Variant
> Dim n As Long
> Dim Rng As Range
> Dim Wks As Worksheet
>
> Set Wks = ActiveSheet
>
> Set Rng = Worksheets("Sheet1").Range("A1").CurrentRegion
> Wks.Columns(1).ClearContents
>
> Data = Rng.Resize(ColumnSize:=2).Value
>
> Set Dict = CreateObject("Scripting.Dictionary")
>
> For i = 1 To UBound(Data, 1)
> Key = Trim(Data(i, 1))
> If Key<> "" Then
> If Not Dict.Exists(Key) Then
> Dict.Add Key, Data(i, 2)
> End If
> If Dict(Key)<> Data(i, 2) Then
> ReDim Preserve MyList(n)
> MyList(n) = Key
> n = n + 1
> End If
> End If
> Next i
>
> If Dict.Count> 0 Then
> Wks.Range("A2").Resize(UBound(MyList, 1) + 1).Value = Application.Transpose(MyList)
> End If
>
> End Sub
>
> --------------------
>
>
> +-------------------------------------------------------------------+
> |Filename: List Multiple Events.zip |
> |Download: http://www.excelbanter.com/attachment.php?attachmentid=481|
> +-------------------------------------------------------------------+
>
>
>

Originally Posted by isabelle

hi,

With Wks
.Range(.Cells(2, 1), .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, 1)).ClearContents
End With


or

With Wks
.Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row).ClearContents
End With

--
isabelle



Le 2012-07-20 13:15, Tee51 a écrit :
> Hello,
>
> I have two problems:
>
> 1) Can someone help me modify the code below so that it pulls data from
> columns other than column A on sheet1?
>
> 2) Can someone help me modify the code below so that the heading in
> column A sheet3 does not clear?
>
> I’m working on a code that will filter through columns H& I in on
> sheet1 and list the values from column H that are listed multiple times
> under different conditions. Sheet1 was used to test the code on text in
> the same sheet. Sheet3 is used to test the code on an active and
> inactive sheet. Simply, I was trying to get the return values to appear
> on a different sheet and it works. But another issue occurred, I cannot
> figure out how to clear the content in column A of sheet3 without
> clearing the heading.
>
>
> Code:
> --------------------
>
> Sub CommandButton1_Click()
>
> Dim Data As Variant
> Dim Dict As Object
> Dim Key As String
> Dim MyList() As Variant
> Dim n As Long
> Dim Rng As Range
> Dim Wks As Worksheet
>
> Set Wks = ActiveSheet
>
> Set Rng = Worksheets("Sheet1").Range("A1").CurrentRegion
> Wks.Columns(1).ClearContents
>
> Data = Rng.Resize(ColumnSize:=2).Value
>
> Set Dict = CreateObject("Scripting.Dictionary")
>
> For i = 1 To UBound(Data, 1)
> Key = Trim(Data(i, 1))
> If Key<> "" Then
> If Not Dict.Exists(Key) Then
> Dict.Add Key, Data(i, 2)
> End If
> If Dict(Key)<> Data(i, 2) Then
> ReDim Preserve MyList(n)
> MyList(n) = Key
> n = n + 1
> End If
> End If
> Next i
>
> If Dict.Count> 0 Then
> Wks.Range("A2").Resize(UBound(MyList, 1) + 1).Value = Application.Transpose(MyList)
> End If
>
> End Sub
>
> --------------------
>
>
> +-------------------------------------------------------------------+
> |Filename: List Multiple Events.zip |
> |Download: http://www.excelbanter.com/attachment.php?attachmentid=481|
> +-------------------------------------------------------------------+
>
>
>

Hi,

Thanks for the response. The first code works perfectly. Do you know how to change the code so that it uses date from column H instead of column A?

yes, you must replace 1 by 8

With Wks
.Range(.Cells(2, 8), .Cells(.Cells(.Rows.Count, 8).End(xlUp).Row, 8)).ClearContents
End With

--
isabelle


> Hi,
>
> Thanks for the response. The first code works perfectly. Do you know how
> to change the code so that it uses date from column H instead of column
> A?

Sorry for the delay.

That suggestion did not help maybe I am missing something.

Sub CommandButton1_Click()
    
    Dim Data As Variant
    Dim Dict As Object
    Dim Key As String
    Dim MyList() As Variant
    Dim n As Long
    Dim Rng As Range
    Dim Wks As Worksheet
        
        Set Wks = ActiveSheet
        
        Set Rng = Worksheets("Sheet1").Range("A1").CurrentRegion
        With Wks
        .Range(.Cells(2, 8), .Cells(.Cells(.Rows.Count, 8).End(xlUp).Row, 8)).ClearContents
        End With


        
        Data = Rng.Resize(ColumnSize:=2).Value
    
        Set Dict = CreateObject("Scripting.Dictionary")
        
            For i = 1 To UBound(Data, 1)
                Key = Trim(Data(i, 1))
                If Key <> "" Then
                    If Not Dict.Exists(Key) Then
                        Dict.Add Key, Data(i, 2)
                    End If
                    If Dict(Key) <> Data(i, 2) Then
                        ReDim Preserve MyList(n)
                        MyList(n) = Key
                        n = n + 1
                    End If
                End If
            Next i
            
        If Dict.Count > 0 Then
            Wks.Range("A2").Resize(UBound(MyList, 1) + 1).Value = Application.Transpose(MyList)
        End If
            
End Sub

hi,

With Worksheets("Sheet1")
.Range(.Cells(1, 8), .Cells(.Cells(.Rows.Count, 8).End(xlUp).Row, 8)).ClearContents
End With


--
isabelle



Le 2012-08-21 15:19, Tee51 a écrit :
> Sorry for the delay.
>
> That suggestion did not help maybe I am missing something.
>
>

That code cleared out Column H and is still returning Column A values.