[Solved] VBA to Find and Remove [closed]

by


Remove Column Titles From Column Data

  • It is assumed that the data (table) starts in cell A1 in worksheet Sheet1 of the workbook containing this code (ThisWorkbook).
  • This is mostly useful for a one-time operation because it will only copy values. It will not copy formulas and formats.
  • Adjust the worksheet (tab) names.
Option Explicit

Sub RemoveTitles()

    ' Constants
    Const sName As String = "Sheet1"
    Const dName As String = "Sheet2"
    
    ' Workbook
    Dim wb As Workbook: Set wb = ThisWorkbook
    
    ' Source
    
    Dim sws As Worksheet: Set sws = wb.Worksheets(sName)
    Dim srg As Range: Set srg = sws.Range("A1").CurrentRegion
    
    ' Data
    
    Dim Data() As Variant: Data = srg.Value
    
    Dim rCount As Long: rCount = UBound(Data, 1)
    Dim cCount As Long: cCount = UBound(Data, 2)
    
    Dim r As Long
    Dim c As Long
    Dim cTitle As String
    
    For c = 1 To cCount
        cTitle = CStr(Data(1, c))
        For r = 2 To rCount
            Data(r, c) = Trim(Replace(Data(r, c), cTitle, vbNullString))
        Next r
    Next c
    
    ' Destination
    
    Dim dws As Worksheet
    On Error Resume Next
        Set dws = wb.Worksheets(dName)
    On Error GoTo 0
    
    If dws Is Nothing Then
        Set dws = wb.Worksheets.Add(After:=sws)
        dws.Name = dName
    Else
        dws.UsedRange.Clear
    End If
    
    Dim drg As Range: Set drg = dws.Range("A1").Resize(rCount, cCount)
    
    drg.Value = Data

End Sub

2

solved VBA to Find and Remove [closed]