Copy Unique Values to a new table VBA

DMO123

Board Regular
Joined
Aug 16, 2018
Messages
99
Hi All,

i have the below code which copies rows from a table that is >0 in a specific column to a new table to eliminate the blanks. the code works perfectly fine for another table and i tried to adjust it to an update i am making but it dose not seem to be copying only values >0 its randomly selecting. for example this code works on table 1 (code below) but for Table 2 it does not (code below)

Table 1 before code (table is in cells A3:B18):

TypeAmount
Test12
Test20
Test37

<tbody>
</tbody>

Table 1 After code (Table is in cells D3:E17):
TypeAmount
Test12
Test37

<tbody>
</tbody>

Code Used (note it is part of a big code hence it does not end with "End Sub"):
Code:
' unique values in typeTotal to type
Dim REP, repT As ListObject
Set REP = ListObjects("type")
Set repT = ListObjects("typeTotal")
REP.Resize Range("D3:E17")
REP.DataBodyRange.ClearContents
repN = 1
For x = 1 To 14
    If repT.DataBodyRange(x, 2) > 0 Then
        REP.ListRows(repN).Range = repT.ListRows(x).Range.Value
        'Range(Cells(CaseT, "N"), Cells(CaseT, "O")) = Range(Cells(x, "K"), Cells(x, "L")).Value
         repN = repN + 1
    End If
Next x
REP.Resize Range("D3:E" & (2 + repN))

Table 2 before code (Table is in cells G34:J64) :
IDAmountSNS
1166
11188
112

<tbody>
</tbody>

Expected results (Table in cells G70:J103):
IDAmountSNS
1166
11188

<tbody>
</tbody>

Code i adjusted but does not work:
Code:
' unique values in STotalAll to Stype
Dim STUDT, SALL As ListObject
Set STUDT = ListObjects("S_Total")
Set SALL = ListObjects("SAll")
STUDT.Resize Range("G70:J103")
STUDT.DataBodyRange.ClearContents
repN = 1
For x = 1 To 32
    If repT.DataBodyRange(x, 8) > 0 Then
        STUDT.ListRows(repN).Range = SALL.ListRows(x).Range.Value
        'Range(Cells(CaseT, "N"), Cells(CaseT, "O")) = Range(Cells(x, "K"), Cells(x, "L")).Value
         repN = repN + 1
    End If
Next x
STUDT.Resize Range("G70:J" & (2 + repN))

the outcome to table 2 is i should not get values that are under 1 but they still show.

i am not too sure where i have gone wrong or if anyone can help but any help is much appreciated!

thanks.
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Watch MrExcel Video

Forum statistics

Threads
1,108,816
Messages
5,525,061
Members
409,617
Latest member
Lenaf

This Week's Hot Topics

Top