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.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,826
Messages
5,855,868
Members
431,771
Latest member
CoryMelth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top