Vba correction removing one column from code

[ANTONIO1981]

Hi All


Below *code works for the following:

- sheet "EMPLOYEE_OH" has 17 columns (A to Q)

- sheet "EMPLOYEE_OH_TABLE" displays 6 columns

The new code needs to take into account the following:in my new sheet "EMPLOYEE_OH" column E is going to disappear
Hence in the new sheet "EMPLOYEE_OH_TABLE column C is going to disappear





*

Sub EMPLOYEE_OH_TABLE()


Dim Ray As Variant, n As Long, Ac As Long, c As Long
Ray = Sheets("EMPLOYEE_OH").Range("A1").CurrentRegion.Resize(, 17)
ReDim nray(1 To UBound(Ray, 1) * UBound(Ray, 2) + 1, 1 To 6)
c = 1
 nray(c, 1) = Ray(1, 1)
 nray(c, 2) = Ray(1, 2)
 nray(c, 3) = Ray(1, 3)
 nray(c, 4) = Ray(1, 4)
 nray(c, 5) = "Date"
 nray(c, 6) = "Amount"
For n = 2 To UBound(Ray, 1)
     For Ac = 5 To UBound(Ray, 2)
        c = c + 1
        nray(c, 1) = Ray(n, 1)
        nray(c, 2) = Ray(n, 2)
        nray(c, 3) = Format(Ray(n, 3), "mmm_yy")
        nray(c, 4) = Ray(n, 4)
        If IsDate(Ray(1, Ac)) Then
                nray(c, 5) = CDate(Ray(1, Ac))
        Else
                nray(c, 5) = Ray(1, Ac)
        End If
        nray(c, 6) = Format(Ray(n, Ac), "#,##0.00000")
    Next Ac
Next n
Sheets("EMPLOYEE_OH_TABLE").Range("A1").Resize(c, 6) = nray
End Sub

thanks in advance

 

[mrmmickle1]

Not to sure what exactly this is doing....but I think it will work if you reduce the columns from 17 to 16 and adjust the numbers = 3 or greater by reducing them by 1.

I can see that Ubound(Ray,1) = The Row Count
I can also see that UBound(Ray, 2) = The Column Count

Not sure why though ... I assume it's shorthand to get the height and width of the array (I don't understand the logic though).

Try these modifications:

Sub EMPLOYEE_OH_TABLE-NEW()
Dim Ray As Variant, n As Long, Ac As Long, c As Long
Ray = Sheets("EMPLOYEE_OH").Range("A1").CurrentRegion.Resize(, 16)
ReDim nray(1 To UBound(Ray, 1) * UBound(Ray, 2) + 1, 1 To 5)
c = 1
 nray(c, 1) = Ray(1, 1)
 nray(c, 2) = Ray(1, 2)
 'nray(c, 3) = Ray(1, 3)
 nray(c, 3) = Ray(1, 3)
 nray(c, 4) = "Date"
 nray(c, 5) = "Amount"
For n = 2 To UBound(Ray, 1)
     For Ac = 5 To UBound(Ray, 2)
        c = c + 1
        nray(c, 1) = Ray(n, 1)
        nray(c, 2) = Ray(n, 2)
        'nray(c, 3) = Format(Ray(n, 3), "mmm_yy")
        nray(c, 3) = Ray(n, 3)
        If IsDate(Ray(1, Ac)) Then
                nray(c, 4) = CDate(Ray(1, Ac))
        Else
                nray(c, 4) = Ray(1, Ac)
        End If
        nray(c, 5) = Format(Ray(n, Ac), "#,##0.00000")
    Next Ac
Next n
Sheets("EMPLOYEE_OH_TABLE").Range("A1").Resize(c, 5) = nray
End Sub

 

[ANTONIO1981]

HI mrmmickle1

MANY THANKS !!!! IT WORKS PERFECT, exactly what i needed

 

[ANTONIO1981]

another question if possible, in sheet "EMPLOYEE_OH" when i delete a row or remove data , it doesnt update in the main table

can we do something in the code to refresh the data?

thanks in advance AC

 

[mrmmickle1]

Do you have some sample data? The code above is written well, but without context it's hard to understand. i.e. it's easier to debug code with a working example. If you could paste some sample info for each of the sheets it would be helpful... 10 or 15 rows each? Try using the link in my signature to help you paste the data to the forum. If it's confidential then just mock it up where the values are the same, data type the same, format the same etc...

 

[ANTONIO1981]

Hi i solved it was basically to: Sheets("EMPLOYEE_OH_TABLE").Cells.ClearContents

many thanks for your help