Windows 7 Excel Macro

#1
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

Windows Forum Team
Staff member
#2
I can you with this in a bit, a powershell script could take care of this easily.
 


Neemobeer

Windows Forum Team
Staff member
#3
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"

Code:
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()
}
 


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


Neemobeer

Windows Forum Team
Staff member
#5
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.
 


#6
Thanks for all your help.
 


This website is not affiliated, owned, or endorsed by Microsoft Corporation. It is a member of the Microsoft Partner Program.