Forum Discussion

Ben Benevento's avatar
Ben Benevento
Copper Contributor
Jul 20, 2018
Solved

VBA Needed for my data cleaning problem

I have a dirty data set and would like to clean it with a VBA script.

 

My data looks like this (also attached);

 

Employee         Category                                            Hours  Amount

Jones, Mary   
 Wages  
 Base Salary $1,923.08
 Total:  $1,923.08
 Superannuation Deductions Before Tax  
 Salary Sacrifice $200.00
 Total:  $200.00
 Taxes  
 PAYG Withholding $400.00
 Total:  $400.00
 Superannuation Expenses  
 Superannuation Guarantee $173.08
 Total:  $173.08
 Entitlements  
 Holiday Leave Accrual3.34 
 Sick Leave Accrual1.34 
 Total: 4.68 
Long, Alan   
 Wages  
 Base Salary $2,307.70
 Total:  $2,307.70
 Taxes  
 PAYG Withholding $624.00
 Total:  $624.00
 Superannuation Expenses  
 Superannuation Guarantee $207.69
 Total:  $207.69
 Entitlements  
 Holiday Leave Accrual3.34 
 Sick Leave Accrual1.34 
 Total: 4.68 

 

I need the staff member name to be copied down and stop in the last empty cell before the next staff members name and then repeat the process for all staff members.

 

Once this is done I can use a pivot table to make better sense of the data.

 

Any assistance would be greatly appreciated.

 

Ben  

Macros & VBA
Need Help
  • Lowell Specht's avatar
    Lowell Specht
    Jul 20, 2018

    There are many ways to do a task in Excel VBA and I'm sure others will give you more efficient ways to do this but this should work.  You can add additional code if you want to remove the assumptions.

     

    Sub FillNames()
        Dim i As Long
        Dim sName As String
        Dim nLastRow As Long
        
        ActiveSheet.UsedRange                   'reset the used range
        nLastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row  'assumes no other data below last person's data
        sName = ""
        
        For i = 1 To nLastRow               'assumes there will always be a name in row 1, column A
            If Cells(i, 1).Value = "" Then  'assumes Employee column is A which is column 1
                Cells(i, 1).Value = sName   'sets the current name in the empty cell
            Else
                sName = Cells(i, 1).Value   'assigns the current row's name
            End If
        Next i
    End Sub

2 Replies

Sort By
  • Lowell Specht's avatar
    Lowell Specht
    Copper Contributor
    Jul 20, 2018

    There are many ways to do a task in Excel VBA and I'm sure others will give you more efficient ways to do this but this should work.  You can add additional code if you want to remove the assumptions.

     

    Sub FillNames()
        Dim i As Long
        Dim sName As String
        Dim nLastRow As Long
        
        ActiveSheet.UsedRange                   'reset the used range
        nLastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row  'assumes no other data below last person's data
        sName = ""
        
        For i = 1 To nLastRow               'assumes there will always be a name in row 1, column A
            If Cells(i, 1).Value = "" Then  'assumes Employee column is A which is column 1
                Cells(i, 1).Value = sName   'sets the current name in the empty cell
            Else
                sName = Cells(i, 1).Value   'assigns the current row's name
            End If
        Next i
    End Sub

Resources