Removing last characters vba

Question

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

You'll need to loop through the cells, read each cell, work out the length of the cell string, subtract 3 and use that as the input to get the n leftmost characters, then write into the target range. — teylyn, Sep 06 '17 at 07:46

h2so4 · Accepted Answer · 2017-09-08T07:05:35.337

3

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

edited Sep 08 '17 at 07:05

answered Sep 06 '17 at 07:46

h2so4

1,559
1
10
11

@Hermes, in lieu of your instruction `inputWS1.Range("A4:A" & lastRow).Copy outputWS.Range("B2")` – h2so4 Sep 07 '17 at 11:29
the line inputWS1.Range("A4:A" & lastRow).Copy outputWS.Range("B2") is working fine for me – Hermes Omar Gomez Sep 07 '17 at 23:36
the actual cells im trying to go from are inputWS3.Range("D2:D") to inputWS3.Range("M2:M") – Hermes Omar Gomez Sep 07 '17 at 23:37
I have modified the code.Put it after the instruction that copies the data to the column M of inputWS3 – h2so4 Sep 08 '17 at 07:06
It Worked! Thanks – Hermes Omar Gomez Sep 13 '17 at 05:36

score 3 · Answer 2 · answered Sep 06 '17 at 08:04

3

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

answered Sep 06 '17 at 08:04

CallumDA

12,025
6
30
52

it gives me a error. im not sure if i understood your comments properly on the code. it tells me to debug on the set rng = – Hermes Omar Gomez Sep 07 '17 at 05:26
@HermesOmarGomez The code works fine as it is above. I can't help you implement it into your code because you haven't shown us your code.. – CallumDA Sep 07 '17 at 12:09
I just edited the post and added my current code. i first tried your code as it is and had no luck. tried some more changes and no luck. Your help is appreciated. – Hermes Omar Gomez Sep 07 '17 at 18:22

Removing last characters vba

2 Answers2

Linked