Empty Cells

You have seen that if you want to step through a column or row of cells until you get to an empty cell, you can use the End property to detect the end of the block. Another way is to examine each cell, one at a time, in a loop structure and stop when you find an empty cell. You can test for an empty cell with the VBA IsEmpty function.

In the spreadsheet shown in Figure 4-13, you want to insert blank rows between each week to produce a report that is more readable.

a; Hi^ooteetï ■ tem

. □ y

a. a

C

E

F S

1

Date I Customer

Product

NumberSold

Price

Revenue

2

Sun Jan 01 2006 Roberts

Oranges

303

15.00

13.545.00

3

Sun Jan 01 2006 Roberts

Oranges

331

15.60

4.965.00

4

Won Jan 02. 2006 Smith

Mangoes

299

20.60

5.980 00

5

Fn Jan 06. 2006 Roberts

Oranges

612

15.00

9,180.00

6

Sun Jan 08. 2006 Roberts

Apples

907

12.50

11.337.50

7

Sun Jan 08. 2006 Pradesh

Pears

107

18.00

1.926.00

a

Tue Jan 10 2006 Roberts

Apples

770

12.50

9.625.Ü0

y

Sat Jan 14 2006 Smith

Apples

223

12 50

2,787.50

10

Sat Jan 14 2006 Smith

Oranges

132

15.00

1.980 00

V

Sun Jan 15. 2006 Pradesh

Oranges

669

15.00

10 035.00

12

Tue Jan 17 2006 Roberts

Mangoes

381

20.00

17.620 00

13

Sat Jan 21. 2006 Kee

Pears

624

18.00

11.232.00

«

Sun Jan 22. 2006 Roberts

Mangoes

193

20.00

3.860 00

»

Mon Jan 23. 2006 Smith

Mangoes

255

20.00

5 100.00

16

Fn Jan 27, 2006 Kee

Mangoes

6

20.00

120 00

B

Fri Jan 27 2006 Kee

Mangoes

311

20.00

6.220.00

18

Sat Jan 28. 2006 Roberts

Oranges

9

15.00

135.00

19

Sat Jan 28. 2006 Kee

Apples

706

12.50

8.825.00

2D

Sun Jan 29, 2006 Kee

Mangoes

441

20.00

8.820 00

21

Mon Jan 30. 2006 Kee

Oranges

336

15.00

14,040.00

22

Fn Feb 03. 2006 Kee

Oranges

301

15.00

13.515.00

23

Sat Feb 04 2006 Smith

Oranges

631

15.00

9.465.00

24

Mon Feb 06 2006 Pradesh

Apples

1S1

12.50

2,262 50

25

Fn Feb 10 2006 Kee

Pears

748

18.00

13 464.00

26

Sat Feb 11 2006 Roberts

Mangoes

533

20.00

11.860 00

Sun Feb 12 2006 Kee

Oranges

B45

15.00

12.675 no

23

Mon Feb 13. 2006 Smith

Apples

742

12.50

9.275 00

29

Fri Feb 17 2006 Smith

Mangoes

804

20.00

16.080.00

3d

Sat Feb 18. 2006 Pradesh

Oranges

863

15.00

12.345.00

qi

^nn FoK "I CI 9nng Pn^ortq

A nnlflc

01

in

m

> m Database BacKu

M—

The following macro compares dates, using the VBA Weekday function to get the day of the week as a number. By default, Sunday is day 1 and Saturday is day 7. If the macro finds that today's day number is less than yesterday's, it assumes a new week has started and inserts a blank row:

Sub ShowWeeks()

Dim iToday As Integer Dim iYesterday As Integer

'Insert empty rows between weeks

Range("A2").Select iYesterday = Weekday(ActiveCell.Value)

'Loop until an empty cell is found Do Until IsEmpty(ActiveCell.Value)

'Select cell below ActiveCell.Offset(1, 0).Select

'Calculate day of week from date in cell iToday = Weekday(ActiveCell.Value)

'If day index has decreased, insert row If iToday < iYesterday Then ActiveCell.EntireRow.Insert ActiveCell.Offset(1, 0).Select End If

'Store latest week day index iYesterday = iToday

Loop End Sub

The result is shown in Figure 4-14.

i rtsniieOtijecO.i-lstti A

Date

Customer

Product

NuiinberSold

Price

Revenue

Sun Jan 01

2006 Roberts

Oranges

903

15.00

13.545.00

Sun Jan 01

2006 Roberts

Oranges

331

15.00

4.965.00

Mon Jan 02. 2006 Smith

Mangoes

299

20.00

5.980 00

Fn Jan 06. 2006 Roberts

Oranges

612

15.00

9 180.00

Sun Jan 08.

2006 Roberts

Apples

907

12 50

11.337 50

Sun Jan 08.

2006 Pradesh

Pears

107

18.00

1.926.00

Tue Jan 10

2006 Roberts

Apples

770

12 50

9,625.00

Sat Jan 14

2006 Smith

Apples

223

12.50

2.787.50

Sat Jan 14

2006 Smith

Oranges

132

15.00

1.980.00

Sun Jan 15

2006 Pradesh

Oranges

669

15.00

10 036.00

Tue Jan 17

2006 Roberts

Mangoes

Bfil

20.00

17 S20 00

Sat Jan 21

2006 Kee

Pears

624

18.00

11.232.00

Sun Jan 22 2006 Roberts Mangoes

Mon Jail 23. 2006 Smith Mangoes

Fri Jan 27 2006 Kee Mangoes

Fri Jan 27, 2006 Kee Mangoes

Sat Jan 28. 2006 Roberts Oranges

Sat Jan 28. 2006 Kee Apples

Sun Jail 29 2006 Kee Mangoes

Men Jan 30. 2006 Kee Oranges

Fn Feb 03. 2006 Kee Oranges

Sat Feb 04 2006 Smith Oranges

Mon Feb 06. 2006 Pradesh Apples

Fn Feb 10. 2006 Kee Pears

Foh 11 snnr Dni,o*c

193

20.00

3.850.00

255

20.00

5.100 00

6

20.00

120.00

311

20.00

6.220.00

a

15.00

135.00

705

12.50

8.825.00

181 748

12 50 18.00 on nn

8.820 00 14 040.00 13.515.00 9 456.00

Figure 4-14

Note that many users detect an empty cell by testing for a zero-length string:

Do Until ActiveCell.Value = ""

This test works in most cases, and would have worked in the previous example, had it been used. However, problems can occur if you are testing cells that contain formulas that can produce zero-length strings, such as the following:

=IF(B2="Kee","Trainee","")

The zero-length string test does not distinguish between an empty cell and a zero-length string resulting from a formula. It is better practice to use the VBA IsEmpty function when testing for an empty cell.

0 0

Post a comment