Re: Delete Row in Excel

[davies]

Corion has pointed out the first problem in your code, but there are several others. In the code below, my version of your code is indented, with the unindented stuff being my setup. Run it, and you will see the first of your issues:

use strict;
use warnings;
use Win32::OLE;

my $xl = Win32::OLE->new('Excel.Application');
$xl->{Visible} = 1;

my $wb = $xl->Workbooks->Add;
my $ws = $wb->Sheets(1);
$ws->Cells(1, 1)->{Value} = "A"; $ws->Cells(1, 2)->{Value} = "E";
$ws->Cells(2, 1)->{Value} = "B";
$ws->Cells(3, 1)->{Value} = "C";
$ws->Cells(4, 1)->{Value} = "D"; $ws->Cells(4, 2)->{Value} = "F";

    my $end = $ws->UsedRange->Rows->{Count};

    for my $count (1..$end) {
        my $cell = $ws->Cells($count, 2);
        if ($cell->{Value} eq "") {
            $cell->EntireRow->Delete;
        }
    }

If I understand your requirements correctly, you want only two rows, but you are getting three. This is because of a logical fault in your code. You check the second row, delete it and then move on to the third row. But you have just moved the third row up to the position of the old second row by the delete command. Trivial. But I have lost count of the number of times I have made this or an equivalent blunder.

Let's look, though, at the differences between your code and my version of it. The first thing we do is find the end row. You search, which is expensive and possibly inaccurate. I ask Excel. I haven't used the search approach or tested my theory, but I would be slightly surprised if I couldn't create a trap in which a search returns the wrong row as the final row. Asking Excel has to be better. Mind you, there are many cases where I would use a boundary row with a named range for lots of other beneficial reasons, but I don't know what you are doing.

I believe that the "for" construction you are using is called a "c style for loop", but I have never used c and can't comment. Having fewer things in the command is both easier to read and less likely to cause a maintenance issue. The final version will actually use your style, but this way is more "Perlish" if you don't need the additional functionality.

There's nothing wrong with your way of generating an address, but using Cells(row, col) can be easier if you are aware of it. Of course, it would be better to have the column - whether letter or number - calculated by the code or as a constant, but that's the problem of cutting code down to show a problem.

Notice how I have defined my $cell to be a reference to the current cell and then used references to that to simplify the code, rather like using the with construct in VBA. Also notice how I have deleted an entire row. I don't like Excel's deletion options, and strongly recommend EntireRow and EntireColumn for clarity and reliability.

Enough of the criticism, how do we make it work? Think backwards. Drunk on golf: 99 Bottles of Beer describes using this technique in an utterly different scenario, but it's a good club to have in your bag. I think that this does what you think you say you ought to want:

use strict;
use warnings;
use Win32::OLE;

my $xl = Win32::OLE->new('Excel.Application');
$xl->{Visible} = 1;

my $wb = $xl->Workbooks->Add;
my $ws = $wb->Sheets(1);
$ws->Cells(1, 1)->{Value} = "A"; $ws->Cells(1, 2)->{Value} = "E";
$ws->Cells(2, 1)->{Value} = "B";
$ws->Cells(3, 1)->{Value} = "C";
$ws->Cells(4, 1)->{Value} = "D"; $ws->Cells(4, 2)->{Value} = "F";

    my $end = $ws->UsedRange->Rows->{Count};

    for (my $count = $end; $count > 1; $count--) {
        my $cell = $ws->Cells($count, 2);
        if ($cell->{Value} eq "") {
            $cell->EntireRow->Delete;
        }
    }

But, as Corion points out, we don't actually know what you want. Let's look at two more possible issues, a zero length string and a formula that evaluates to a zero length string:

use strict;
use warnings;
use Win32::OLE;

my $xl = Win32::OLE->new('Excel.Application');
$xl->{Visible} = 1;

my $wb = $xl->Workbooks->Add;
my $ws = $wb->Sheets(1);
$ws->Cells(1, 1)->{Value} = "A"; $ws->Cells(1, 2)->{Value} = "E";
$ws->Cells(2, 1)->{Value} = "B"; $ws->Cells(2, 2)->{Value} = "'";
$ws->Cells(3, 1)->{Value} = "C"; $ws->Cells(3, 2)->{Formula} = '=If(0,
+"Extremely long formula that in this case evaluates to a zero length 
+string","")';
$ws->Cells(4, 1)->{Value} = "D"; $ws->Cells(4, 2)->{Value} = "F";

    my $end = $ws->UsedRange->Rows->{Count};

    for (my $count = $end; $count > 1; $count--) {
        my $cell = $ws->Cells($count, 2);
        if ($cell->{Value} eq "") {
            $cell->EntireRow->Delete;
        }
    }

Note that B2 and B3 are both set up to contain data, but get deleted. If you have been watching very carefully, you will have noticed that in the previous examples, we got warnings whenever there was a line being deleted because the value was undefined. This we can leverage:

use strict;
use warnings;
use Win32::OLE;

my $xl = Win32::OLE->new('Excel.Application');
$xl->{Visible} = 1;

my $wb = $xl->Workbooks->Add;
my $ws = $wb->Sheets(1);
$ws->Cells(1, 1)->{Value} = "A"; $ws->Cells(1, 2)->{Value} = "E";
$ws->Cells(2, 1)->{Value} = "B"; $ws->Cells(2, 2)->{Value} = "'";
$ws->Cells(3, 1)->{Value} = "C"; $ws->Cells(3, 2)->{Formula} = '=If(0,
+"Extremely long formula that in this case evaluates to a zero length 
+string","")';
$ws->Cells(4, 1)->{Value} = "D"; $ws->Cells(4, 2)->{Value} = "F";

    my $end = $ws->UsedRange->Rows->{Count};

    for (my $count = $end; $count > 1; $count--) {
        my $cell = $ws->Cells($count, 2);
        if (!defined($cell->{Value})) {
            $cell->EntireRow->Delete;
        }
    }

This is where we don't know what you want. If you want to delete zero length strings but not formulae, there are ways around this problem, but it's nearly bedtime & I have a busy day ahead.

One final point. Do you see how I have set up Excel from scratch in every bit of code, including example data? Your code can't be run, while mine can. If it is possible (it sometimes isn't), it makes other peoples' lives easier to show runnable code. You aren't the first and won't be the last to show only partial code - I've done it when necessary - but if runnable code can be shown, better solutions are likely.

Regards,

John Davies