CODEWITHSUNDEEP
vbaexcel
Hermes Omar Gomez
Hermes Omar Gomez

Reputation: 67

Removing last characters vba

I have a column that has a set of Numbers on each cell.

Im simply looking to copy the data on the column til the last row leaving behind the last 3 digits on each cell.

I have a variable for the last row: LASTROW and a range already set.

My code kinda looks like this

inputWS1.Range("A4:A" & lastRow).Copy outputWS.Range("B2")

As i said, im looking to copy data from A4 to B2 and on, but i want to remove the last 3 characters on A4 when they move to B2 on the other sheet.

Please Help and thanks for all your help!

edit: my current code is :

Private Sub Update_Click()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim path As String, fileName As String
Dim lastRowUniversal As Long, lastRowOutput As Long, rowCntr As Long, lastColumn As Long
Dim inputWS1 As Worksheet, inputWS2 As Worksheet, inputWS3 As Worksheet, outputWS As Worksheet
Dim rng As Range
Dim arr As Variant
Dim i As Long
Const dtFORM As String = "=IF(ISNUMBER(J4:J<r>),DATE(YEAR(J4:J<r>)-1," & "MONTH(J4:J<r>),DAY(J4:J<r>)),J4:J<r>)"



'set your sheets here
Set inputWS1 = ThisWorkbook.Sheets("Universal")
Set inputWS2 = ThisWorkbook.Sheets("Geovera")
Set inputWS3 = ThisWorkbook.Sheets("Citizens")
Set outputWS = ThisWorkbook.Sheets("Carriers")
Set rng = inputWS3.Range("D2:D")
arr = inputWS3.Range("M2:M" & LastRowCitizens)

    'get last rows from both sheets
lastRowUniversal = inputWS1.Cells(Rows.Count, "A").End(xlUp).Row
LastRowCitizens = inputWS3.Cells(Rows.Count, "A").End(xlUp).Row
lastRowGeovera = inputWS2.Cells(Rows.Count, "A").End(xlUp).Row
LastRowPolicy = outputWS.Cells(Rows.Count, "B").End(xlUp).Row
lastRowOutput = outputWS.Cells(Rows.Count, "A").End(xlUp).Row
lastColumn = inputWS1.Cells(1, Columns.Count).End(xlToLeft).Column

    'get last rows from both sheets
lastRowUniversal = inputWS1.Cells(Rows.Count, "A").End(xlUp).Row
LastRowCitizens = inputWS3.Cells(Rows.Count, "A").End(xlUp).Row
lastRowGeovera = inputWS2.Cells(Rows.Count, "A").End(xlUp).Row
LastRowPolicy = outputWS.Cells(Rows.Count, "B").End(xlUp).Row
lastRowOutput = outputWS.Cells(Rows.Count, "A").End(xlUp).Row
lastColumn = inputWS1.Cells(1, Columns.Count).End(xlToLeft).Column

rowCntr = 1

For i = 1 To UBound(arr)
arr(i) = Left(arr(i), Len(arr(i)) - 3)
Next i


'get last rows from both sheets
lastRowUniversal = inputWS1.Cells(Rows.Count, "A").End(xlUp).Row
LastRowCitizens = inputWS3.Cells(Rows.Count, "A").End(xlUp).Row
lastRowGeovera = inputWS2.Cells(Rows.Count, "A").End(xlUp).Row
LastRowPolicy = outputWS.Cells(Rows.Count, "B").End(xlUp).Row
lastRowOutput = outputWS.Cells(Rows.Count, "A").End(xlUp).Row
lastColumn = inputWS1.Cells(1, Columns.Count).End(xlToLeft).Column

'Universal
inputWS1.Range("A4:A" & lastRowUniversal).Copy outputWS.Range("B2")
inputWS1.Range("B4:B" & lastRowUniversal).Copy outputWS.Range("C2")
inputWS1.Range("N4:N" & lastRowUniversal).Value = inputWS1.Name
inputWS1.Range("N4:N" & lastRowUniversal).Copy outputWS.Range("E2")
inputWS1.Range("L4:L" & lastRowUniversal).Value = inputWS1.Evaluate(Replace(dtFORM, "<r>", lastRowUniversal))
inputWS1.Range("L4:L" & lastRowUniversal).Copy outputWS.Range("G2")
inputWS1.Range("G4:G" & lastRowUniversal).Copy outputWS.Range("H2")

'Geovera
inputWS2.Range("F2:F" & lastRowGeovera).Copy outputWS.Range("B" & lastRowUniversal - 1)
inputWS2.Range("I2:I" & lastRowGeovera).Copy outputWS.Range("C" & lastRowUniversal - 1)
inputWS2.Range("P2:P" & lastRowGeovera).Value = inputWS2.Name
inputWS2.Range("P2:P" & lastRowGeovera).Copy outputWS.Range("E" & lastRowUniversal - 1)
inputWS2.Range("N2:N" & lastRowGeovera).Copy outputWS.Range("H" & lastRowUniversal - 1)
inputWS2.Range("G2:G" & lastRowGeovera).Copy outputWS.Range("G" & lastRowUniversal - 1)

'Citizens
inputWS3.Range("M2:M" & LastRowCitizens).Value = arr






Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Upvotes: 0

Views: 6755

Answers (2)

h2so4
h2so4

Reputation: 1577

use the left function

With inputWS3
    For i = 2 To lastrow
        .Cells(i, "M") = Left(.Cells(i, "M").Value, Len(.Cells(i, "M").Value) - 3)
    Next i
End With

Upvotes: 3

CallumDA
CallumDA

Reputation: 12113

As h2so4 pointed out Left and Len are a good way to go. However, in terms of implementing something like this, it can be quite important to loop through an array rather than the range. It will be much quicker if you've got lots of data.

Sub Test()
    Dim rng As Range
    Dim arr As Variant
    Dim i As Long

    Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A1:A1000") 'your input range
    arr = Application.Transpose(rng) 'fill variant array with values from range

    For i = 1 To UBound(arr)
        arr(i) = Left(arr(i), Len(arr(i)) - 3) 'loop through array removing last three chars
    Next i

    rng.Value = Application.Transpose(arr) 'return array to the worksheet in one go
End Sub

Upvotes: 3

Related Questions