CODEWITHSUNDEEP
excelvba
Memo
Memo

Reputation: 25

Make all active cells in a specific Column to its Absolute Value

Can you help me correct my VBA codes. I want to convert the values of Column U until the active row to Absolute Values meaning to remove the negative amounts.
Here is my VBA code:

Sub MakeColumnsAbsoluteValue()

    Dim sht As Worksheet
    Dim rngToAbs As Range
    Dim LastRow As Long
    Dim c As Range

    Set sht = ThisWorkbook.Sheets("MJEBlackline")
    LastRow = sht.Cells(sht.Rows, Count, "U").End(xlUp).Row
    Set rngToAbs = Range("U5:U" & LastRow)

    For Each c In rngToAbs
        c.Value = Abs(c.Value)

    Next c


End Sub

Upvotes: 2

Views: 1489

Answers (3)

Error 1004
Error 1004

Reputation: 8230

You could try:

Option Explicit

Sub MakeColumnsAbsoluteValue()

    Dim sht As Worksheet
    Dim rngToAbs As Range, c As Range
    Dim LastRow As Long, x As Long
    Dim arr() As Variant

    Set sht = ThisWorkbook.Sheets("MJEBlackline")
    x = 0

    With sht

        LastRow = .Cells(.Rows.Count, "U").End(xlUp).Row

        Set rngToAbs = .Range("U5:U" & LastRow)

        'Loop range and create an array including all abs values
        For Each c In rngToAbs
            ReDim Preserve arr(x)
            arr(x) = Abs(c.Value)
            x = x + 1
        Next c

        'Paste the values of the array at once instead of pasting values one by one
        .Range("U5:U" & LastRow).Value = Application.WorksheetFunction.Transpose(arr)

    End With

End Sub

Upvotes: 0

JvdV
JvdV

Reputation: 75900

You may try:

Sub MakeColumnsAbsoluteValue()

Dim sht As Worksheet
Dim rngToAbs As Range
Dim LastRow As Long

Set sht = ThisWorkbook.Sheets("MJEBlackline")
With sht
    LastRow = .Cells(.Rows.Count, "U").End(xlUp).Row
    Set rngToAbs = .Range("U5:U" & LastRow)
    rngToAbs.Value = .Evaluate("=abs(" & rngToAbs.Address & ")")
End With

End Sub

Or even (inspired through @GarysStudent):

Sub MakeColumnsAbsoluteValue()

Dim sht As Worksheet
Dim rngToAbs As Range
Dim LastRow As Long

Set sht = ThisWorkbook.Sheets("MJEBlackline")
With sht
    LastRow = .Cells(.Rows.Count, "U").End(xlUp).Row
    Set rngToAbs = .Range("U5:U" & LastRow)
    rngToAbs.Replace what:="-", lookat:=xlPart, replacement:=""
End With

End Sub

This would both convert the whole range in one go. Assuming that's what you meant with:

"I want to convert the values of Column U until the active row..."

Upvotes: 2

Mikku
Mikku

Reputation: 6664

Problem with line LastRow = sht.Cells(sht.Rows, Count, "U").End(xlUp).Row

Use of , instead of . and not specifying the sheet reference in rngToAbs

Try:

Sub MakeColumnsAbsoluteValue()

    Dim sht As Worksheet
    Dim rngToAbs As Range
    Dim LastRow As Long
    Dim c As Range

    Set sht = ThisWorkbook.Sheets("FF")
    LastRow = sht.Cells(sht.Rows.count, "U").End(xlUp).row
    Set rngToAbs = sht.Range("U5:U" & LastRow)

    For Each c In rngToAbs
        c.Value = Abs(c.Value)

    Next c


End Sub

Upvotes: 3

Related Questions