YaleCoder
(Getting the hang of it)
2008-03-04 09:18 PM
How does one delete rows in excel

I am looking for the syntax for deleting rows in excel. I have looked all over but to no avail. I tried the xlRangeDelete() UDF but it doesn't do what I need as the sheets are protected but users have the right to delete entire rows.

Thanks

Witto
(MM club member)
2008-03-04 09:29 PM
Re: How does one delete rows in excel

Maybe first Select the row: Rows("xx:yy").Select
than do a Selection.Clear

YaleCoder
(Getting the hang of it)
2008-03-04 09:47 PM
Re: How does one delete rows in excel

I'll give that a try but won't that just clear the contents of the cells? I need to delete the row completely. I am populating a template that is designed to accommodate a large subset of data but for the records that just populate a few line items, I want to delete the excess rows. Maybe I could hide them. As long as they don't print, that would work too.

AllenAdministrator
(KiX Supporter)
2008-03-04 09:58 PM
Re: How does one delete rows in excel

This shouldn't be to difficult to convert to Kix...

How Can I Delete Specified Rows in an Excel Spreadsheet?
http://www.microsoft.com/technet/scriptcenter/resources/qanda/jun07/hey0622.mspx

Mart
(KiX Supporter)
2008-03-04 10:08 PM
Re: How does one delete rows in excel

Something like this.
I tried to convert the VBScript in the article above to Kix but I can’t get it to enter the loop.
My guess is that is has something to do with this line $objExcel.Cells($i, 1).Value = "". I'm not sure if this is proper kix syntax. The script below hangs on it. All lines above it execute properly but it stops when it should enter the loop.

 Code:
Break on

$objExcel = CreateObject("Excel.Application")
$objExcel.Visible = True

$objWorkbook = $objExcel.Workbooks.Open("D:\Test.xlsx")

$i = 1

Do Until $objExcel.Cells($i, 1).Value = ""
    If $objExcel.Cells($i, 1).Value = "delete"
        $objRange = $objExcel.Cells($i, 1).EntireRow
        $objRange.Delete
        $i = $i - 1
    EndIf
    $i = $i + 1
Loop


[edit]
Before someone starts shouting, the d:\text.xlsx (note the extra X) is not the issue. I have Office 2007 \:\)
[/edit]

AllenAdministrator
(KiX Supporter)
2008-03-04 10:15 PM
Re: How does one delete rows in excel

well maybe this has something to do with it...

 Code:
Do Until ....

Loop


Would "While" work better?

Mart
(KiX Supporter)
2008-03-04 10:22 PM
Re: How does one delete rows in excel

@#$!%#$^@#$ Feeling kinda stupid Thanks Allen.

Kix also has a Do Until but that would be Do ;stuff until $a = $b. Sure this can also be done with a Do until loop just not as I had it first. The script below works.

 Code:
Break on

$objExcel = CreateObject("Excel.Application")
$objExcel.Visible = True

$objWorkbook = $objExcel.Workbooks.Open("D:\Test.xlsx")

$i = 1

While $objExcel.Cells($i, 1).Value <> ""
    If $objExcel.Cells($i, 1).Value = "delete"
        $objRange = $objExcel.Cells($i, 1).EntireRow
        $objRange.Delete
        $i = $i - 1
    EndIf
    $i = $i + 1
Loop


AllenAdministrator
(KiX Supporter)
2008-03-04 10:27 PM
Re: How does one delete rows in excel

 Quote:
@#$!%#$^@#$ Feeling kinda stupid


LOL... been there, done that many times

Mart
(KiX Supporter)
2008-03-04 10:40 PM
Re: How does one delete rows in excel

Yeah it's a easy mistake to make.

To quote Captain Alberto Bertorelli from the tv serie Allo, Allo.

Whata mistaka ta maka!!

YaleCoder
(Getting the hang of it)
2008-03-04 10:46 PM
Re: How does one delete rows in excel

What a great show. I just watched the first 6 seasons...Funny as hell! I'll try the script above tomorrow and let you know how it works for me!

Thanks

Arend_
(MM club member)
2008-03-10 10:29 AM
Re: How does one delete rows in excel

 Originally Posted By: Herr Flick
How many germans does it take to screw in a light bulb?

 Originally Posted By: Von Smallhousen
1?

 Originally Posted By: Herr Flick
VE ASK ZE QUESTIONS!!