About Us

Tee51

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

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|
+-------------------------------------------------------------------+

Tee51

Quote:

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?

isabelle

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?

Tee51 · 08:27 PM

Sorry for the delay.

That suggestion did not help maybe I am missing something.

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
        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

isabelle

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.

Tee51

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Returning a value if multiple conditions are met	Joel	Excel Worksheet Functions	2	May 29th 10 03:05 AM
Returning a value if multiple conditions are met	Jacob Skaria	Excel Worksheet Functions	0	May 27th 10 09:28 PM
Looking up multiple values and returning one corresponding value	Nightrain	Excel Worksheet Functions	10	September 2nd 08 03:55 PM
returning values from columns to another sheet, based on conditions	zangief	Excel Programming	1	September 26th 05 08:13 PM
How to calculate values in multiple values with multi conditions	Curtis	Excel Worksheet Functions	2	July 15th 05 02:36 AM

Menu

About Us