- 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!
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
- Joined
- Jul 4, 2015
- Messages
- 8,998
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"
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()
}