📎 AI Summary:
The thread discusses a user seeking a method to automate deleting blank rows in Excel, specifically those with empty first columns. Neemobeer suggests using a PowerShell script to accomplish this, providing detailed instructions and code. The original poster later mentions that PowerShell has been disabled on their system, but others offer potential solutions like running PowerShell with administrator privileges, ultimately expressing appreciation for the assistance.
R

rc0213

New Member
Member details
Joined
Oct 13, 2016
Messages
6
Thread Author #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!
 

Solution
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"

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...
Sort by date Sort by votes
Neemobeer

Neemobeer

Windows Forum Team
Staff member
Member details
Joined
Jul 4, 2015
Messages
8,995
#2
I can you with this in a bit, a powershell script could take care of this easily.
 

Upvote 0 Downvote
Neemobeer

Neemobeer

Windows Forum Team
Staff member
Member details
Joined
Jul 4, 2015
Messages
8,995
#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()
}
 

Upvote 1 Downvote
Solution
R

rc0213

New Member
Member details
Joined
Oct 13, 2016
Messages
6
Thread Author #4
Thanks! The problem I have now is that the company disabled PowerShell on the system. I appreciate your help though.
 

Upvote 0 Downvote
Neemobeer

Neemobeer

Windows Forum Team
Staff member
Member details
Joined
Jul 4, 2015
Messages
8,995
#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.
 

Upvote 0 Downvote
You must log in or register to reply here.