I need help to remove character from a column with particular header.

I have a spreadsheet with headers (IP, hostname, Asset Group). I need to remove the first 3 characters from each row of the column called "Asset Group" (excluding header name (row 1)). Basically, I want to get rid of the "VM " from that column. I have a problem how to refer to that particular column "Asset Group". Thank you in advance!

    Dim SelRange As Range
    Dim ColNum As Integer
    Dim CWS As Worksheet
    
        'Find the column number where the column header is Asset Group
    
        Set CWS = ActiveSheet
        ColNum = Application.WorksheetFunction.Match("Asset Group", CWS.Rows(1), 0)
        
        LastColumn = Cells(1, CWS.Columns.Count).End(xlToLeft).Column
        For i = 2 To LastColumn
        
            ...
        
        Next i
    
    End With
    
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

End Sub

CodePudding user response：

You can try something like the following

Dim CWS As Worksheet
Dim AssetHdr as Range
Dim tmp as Variant
Dim i as Long

Set CWS = ActiveSheet

' Find Header Column
Set AssetHdr = CWS.Rows(1).Find(what:="Asset Group")

' Test if Header was found
If Not AssetHdr is Nothing Then
    With CWS
        ' Get Last Row in Column
        NoRow = .Cells(.Rows.Count, AssetHdr.Column).End(xlUp).Row
        ' Store in array
        tmp = Application.Transpose(.Range(.Cells(2, AssetHdr.Column), .Cells(NoRow, AssetHdr.Column))).Value2
        ' Remove first 3 characters
        For i = LBound(tmp) to UBound(tmp)
            tmp(i) = Right(tmp(i), Len(tmp(i))-3)
        Next i
        ' Write back to sheet
        .Cells(2, AssetHdr.Column).Resize(ubound(tmp)-1).Value2 = tmp
    End With
End If

CodePudding user response：

Replace Partial Strings in a Range

• Application.Match allows you to test its result with IsError or IsNumeric, while WorksheetFunction.Match raises an error if no match.
• With the Range.Replace method you can replace values in a range in one go.

Option Explicit

Sub ReplacePartialString()
    
    Dim cws As Worksheet: Set cws = ActiveSheet
    
    Dim ColNum As Variant
    ColNum = Application.Match("Asset Group", cws.Rows(1), 0)
    
    If IsError(ColNum) Then
        MsgBox "Column 'Asset Group' not found.", vbCritical
        Exit Sub
    End If
    
    Dim LastCell As Range
    Set LastCell = cws.Cells(cws.Rows.Count, ColNum).End(xlUp)
    
    If LastCell.Row < 2 Then
        MsgBox "No data found.", vbCritical
        Exit Sub
    End If
    
    Dim crg As Range: Set crg = cws.Range(cws.Cells(2, ColNum), LastCell)
    
    crg.Replace "VM ", "", xlPart, , True
    
End Sub