Listing Using Replace Programmatically to Set the Correct Range

Sub ReplaceExample() Dim ws As Worksheet Dim rg As Range Dim lLastRow As Long

Set ws = ThisWorkbook.Worksheets("Replace Examples")

' determine last cell in data range ' assumes the would never be an empty cell ' in column 1 at the bottom of the list lLastRow = ws.Ce11s(65536, 1).End(x1Up).Row

' Replace empty cells in 2nd & 3rd columns

Set rg = ws.Range(ws.Ce11s(2, 2), ws.Ce11s(1LastRow, 3))

rg.Replace "", "UNKNOWN"

' Replace empty cells in 4th column

Set rg = ws.Range(ws.Ce11s(2, 4), ws.Ce11s(1LastRow, 4)) rg.Replace "", "0"

Set rg = Nothing Set ws = Nothing End Sub

More times than not, when you're working with a list, at least one column always has a value for every row in the list. This is the column that you'll want to use to determine what the last row is. If you use a column that potentially has an empty value in the last cell, you won't be checking all of the rows in the list when you use Replace. In this example, the first column contains a value in every row, so it is the column you'd use to find the last row in the list. As you recall from the last chapter, the End property of the Range object is a convenient way to do this. All you do is start from the last row in the worksheet (I cheated here and hard coded the number of rows in a worksheet) and use the End property with the xlUp constant to find the first non-empty cell in the column. Because End returns a Range object, you use the Row property to give you your answer—a numeric value that represents the last row in your list.

Once you have the last row, it's simple to set the appropriate range references for use with the Replace method. First you set a range to the second and third columns. This is easily achieved by specifying the top-left and bottom-right cells that define the range.

Set rg = ws.Range(ws.Ce11s(2, 2), ws.Ce11s(1LastRow, 3))

The top-left cell is given using the Cells property of your worksheet object and refers to the cell in row 2, column 2. The bottom-right cell is specified similarly except you use the value in the lLastRow variable for your row along with column 3. The range reference for the fourth column is set in the same manner.

The actual call to the Replace method is no big deal. All you need to do is tell Replace what to look for along with what value it should replace any instances it finds with.

OK, so that is easy enough. But what the heck is that Application.FindFormat thing all about? Application.FindFormat and Application.ReplaceFormat are objects that exist solely to allow you to describe what kind of formatting Replace should look for and then what kind of formatting should be applied to anything Replace finds. Listing 9.3 shows an example that looks on the active sheet for any cells that have bold, size 11 font and replaces the formatting with italic, size 8.

0 0

Post a comment