Windows 7 Need Help Creating an Excel Macro to Delete Empty Rows Based on First Column

[rc0213]

Hi,

I am trying to extract information from one program, then import it into Excel. Well, the format it is extracted is not the format that is what I need. So, I have been manually deleting all the unneeded rows. My question is there a macro or C-Plus program that can delete these rows for me?

The rows I want to delete will not have data in the first column, where the ones I want to keep do. What is the easiest way to doing this, macro, C-Plus, etc.? And, if it is a macro, is there a macro you recommend? Or, can someone create this for me since I am not a programmer.

Thank you for all your help!

 

[Neemobeer]

I can you with this in a bit, a powershell script could take care of this easily.

 

[Neemobeer]

Save this as a .ps1 file, open a powershell prompt and load it . <Path to this file>
Then type Remove-Rows -FilePath "Path to excel file"

Function Remove-Rows
{
    param (
    [string]$FilePath)

    $excel = New-Object -ComObject Excel.Application
    $excel.Visible = $false
    $workbook = $excel.Workbooks.Open($FilePath)
    $worksheet = $workbook.ActiveSheet()
    $rowCount = ($worksheet.UsedRange.Rows).Count

    

    for($row = 1;$row -lt $rowCount; $row++)
    {
       if(($($worksheet.Cells.Item($row,1).value2) -eq $null) -or ($($worksheet.Cells.Item($row,1).value2) -eq ""))
       {
            $deleteThis = $worksheet.Cells.Item($row,1).EntireRow
            $deleteThis.Delete()
            Write-Host "Delete this row: $row"
            $row--
       }
    }
    $workbook.Save()
    $excel.Workbooks.Close()
}

 

[rc0213]

Thanks! The problem I have now is that the company disabled PowerShell on the system. I appreciate your help though.

 

[Neemobeer]

If you have admin rights, you can open an elevated powershell prompt and type Set-ExecutionPolicy -ExecutionPolicy Bypass to run scripts in the current window.

 

[rc0213]

Thanks for all your help.