asked on

select data 4N DCC

need select first digit of column GJ

then add the 2 digit of column GL

then show together in column IE

as show here

here the file
4N_BRO_DCC_to.xlsm

Subodh Tiwari (Neeraj)

Try this...

Sub DCC()
Dim lr As Long
Dim Rng As Range, Cell As Range
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "GJ").End(xlUp).Row
Set Rng = Range("GJ6:GJ" & lr)
For Each Cell In Rng
    If IsNumeric(Cell.Value) And IsNumeric(Cell.Offset(0, 2).Value) Then
        Cells(Cell.Row, "IE").NumberFormat = "@"
        Cells(Cell.Row, "IE") = Left(Cell.Value, 1) & Left(Cell.Offset(0, 2).Value, 2)
    End If
Next Cell
Application.ScreenUpdating = True
End Sub

Open in new window

4N_BRO_DCC_to.xlsm

ADRIANA P

ASKER

Subodh Tiwari (Neeraj)

working as needed the only detail

is taking long time

can be improve the time ?

Subodh Tiwari (Neeraj)

Okay, try this...
Replace the existing code on Module3 with the following code.

Sub DCC()
Dim lr As Long, i As Long
Dim x, y()
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "GJ").End(xlUp).Row
x = Range("GJ6:GL" & lr).Value
ReDim y(1 To UBound(x, 1), 1 To 1)
For i = 1 To UBound(x, 1)
    If IsNumeric(x(i, 1)) And IsNumeric(x(i, 3)) Then
        y(i, 1) = Left(x(i, 1), 1) & Left(x(i, 3), 2)
    End If
Next i
Range("IE6").Resize(UBound(y), 1).Value = y
Application.ScreenUpdating = True
End Sub

Open in new window

ADRIANA P

ASKER

Subodh Tiwari (Neeraj)

im not an computer Expert !

Subodh Tiwari (Neeraj)

You uploaded a large sample file, it's taking time to upload it back.
I am trying to upload it. :)

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial