Page 1 of 1 1
Topic Options
#185934 - 2008-03-04 09:18 PM How does one delete rows in excel
YaleCoder Offline
Getting the hang of it

Registered: 2001-12-18
Posts: 61
Loc: Jelenia Gora, Poland
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
_________________________
"When fascism comes to America, it will be wrapped in the flag, carrying a cross."
Sinclair Lewis

Top
#185935 - 2008-03-04 09:29 PM Re: How does one delete rows in excel [Re: YaleCoder]
Witto Offline
MM club member
*****

Registered: 2004-09-29
Posts: 1828
Loc: Belgium
Maybe first Select the row: Rows("xx:yy").Select
than do a Selection.Clear

Top
#185938 - 2008-03-04 09:47 PM Re: How does one delete rows in excel [Re: Witto]
YaleCoder Offline
Getting the hang of it

Registered: 2001-12-18
Posts: 61
Loc: Jelenia Gora, Poland
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.
_________________________
"When fascism comes to America, it will be wrapped in the flag, carrying a cross."
Sinclair Lewis

Top
#185939 - 2008-03-04 09:58 PM Re: How does one delete rows in excel [Re: YaleCoder]
Allen Administrator Online   shocked
KiX Supporter
*****

Registered: 2003-04-19
Posts: 4545
Loc: USA
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

Top
#185940 - 2008-03-04 10:08 PM Re: How does one delete rows in excel [Re: Allen]
Mart Moderator Offline
KiX Supporter
*****

Registered: 2002-03-27
Posts: 4672
Loc: The Netherlands
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]


Edited by Mart (2008-03-04 10:10 PM)
Edit Reason: Added comment.
_________________________
Mart

- Chuck Norris once sold ebay to ebay on ebay.

Top
#185941 - 2008-03-04 10:15 PM Re: How does one delete rows in excel [Re: Mart]
Allen Administrator Online   shocked
KiX Supporter
*****

Registered: 2003-04-19
Posts: 4545
Loc: USA
well maybe this has something to do with it...

 Code:
Do Until ....

Loop


Would "While" work better?

Top
#185942 - 2008-03-04 10:22 PM Re: How does one delete rows in excel [Re: Allen]
Mart Moderator Offline
KiX Supporter
*****

Registered: 2002-03-27
Posts: 4672
Loc: The Netherlands
@#$!%#$^@#$ 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

_________________________
Mart

- Chuck Norris once sold ebay to ebay on ebay.

Top
#185943 - 2008-03-04 10:27 PM Re: How does one delete rows in excel [Re: Mart]
Allen Administrator Online   shocked
KiX Supporter
*****

Registered: 2003-04-19
Posts: 4545
Loc: USA
 Quote:
@#$!%#$^@#$ Feeling kinda stupid


LOL... been there, done that many times

Top
#185944 - 2008-03-04 10:40 PM Re: How does one delete rows in excel [Re: Allen]
Mart Moderator Offline
KiX Supporter
*****

Registered: 2002-03-27
Posts: 4672
Loc: The Netherlands
Yeah it's a easy mistake to make.

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

Whata mistaka ta maka!!
_________________________
Mart

- Chuck Norris once sold ebay to ebay on ebay.

Top
#185945 - 2008-03-04 10:46 PM Re: How does one delete rows in excel [Re: Mart]
YaleCoder Offline
Getting the hang of it

Registered: 2001-12-18
Posts: 61
Loc: Jelenia Gora, Poland
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
_________________________
"When fascism comes to America, it will be wrapped in the flag, carrying a cross."
Sinclair Lewis

Top
#186081 - 2008-03-10 10:29 AM Re: How does one delete rows in excel [Re: Mart]
Arend_ Moderator Offline
MM club member
*****

Registered: 2005-01-17
Posts: 1894
Loc: Hilversum, The Netherlands
 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!!

Top
Page 1 of 1 1


Moderator:  Shawn, ShaneEP, Ruud van Velsen, Arend_, Jochen, Radimus, Glenn Barnas, Allen, Mart 
Hop to:
Shout Box

Who's Online
2 registered (morganw, mole) and 414 anonymous users online.
Newest Members
gespanntleuchten, DaveatAdvanced, Paulo_Alves, UsTaaa, xxJJxx
17864 Registered Users

Generated in 0.063 seconds in which 0.023 seconds were spent on a total of 13 queries. Zlib compression enabled.

Search the board with:
superb Board Search
or try with google:
Google
Web kixtart.org