I have a button on my excel with the following code. I have to click on it
twice, each time, for it to delete rows that contains "!No_PRorFB_cdt" in
column B. Is there something I am doing wrong here?

Private Sub cmdBuildKML_Click()
Dim siteDataws As Worksheet, DataDLws As Worksheet
Dim siteDataws_lastRow As Long, DataDLws_lastRow As Long, i As Integer
Dim c As Range, rngAll As Range

Application.DisplayAlerts = False
Set siteDataws = Worksheets("GoogleEarth_SiteData")
Set DataDLws = Worksheets("DATA DOWNLOAD")
siteDataws_lastRow = siteDataws.Range("B65536").End(xlUp).Row
DataDLws_lastRow = DataDLws.Range("A65536").End(xlUp).Row

DataDLws.Range("R2:R" & DataDLws_lastRow & ", T2:T" & DataDLws_lastRow).Copy
siteDataws.Select
siteDataws.Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
DataDLws.Range("BJ2:BJ" & DataDLws_lastRow, "BK2:BK" & DataDLws_lastRow).Copy
siteDataws.Select
siteDataws.Range("F3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Range("C3").Select

For Each c In siteDataws.Range("B3:B" & siteDataws_lastRow).Cells
If c < "" And c = "!No_PRorFB_cdt" Then
If rngAll Is Nothing Then
Set rngAll = c
Else
Set rngAll = Union(rngAll, c)
End If
End If
Next c
If Not rngAll Is Nothing Then rngAll.EntireRow.Delete

i = 1
siteDataws_lastRow = siteDataws.Range("B65536").End(xlUp).Row
For Each c In siteDataws.Range("B3:B" & siteDataws_lastRow).Cells
If c < "" Then
c.Offset(0, -1) = i
i = i + 1
End If
Next c

Application.DisplayAlerts = False
End Sub

Disregard!!
I figured out what I did wrong. This line of code
"Set siteDataws = Worksheets("GoogleEarth_SiteData")" was in the wrong
location.


"Ayo" wrote:

I have a button on my excel with the following code. I have to click on it
twice, each time, for it to delete rows that contains "!No_PRorFB_cdt" in
column B. Is there something I am doing wrong here?

Private Sub cmdBuildKML_Click()
Dim siteDataws As Worksheet, DataDLws As Worksheet
Dim siteDataws_lastRow As Long, DataDLws_lastRow As Long, i As Integer
Dim c As Range, rngAll As Range

Application.DisplayAlerts = False
Set siteDataws = Worksheets("GoogleEarth_SiteData")
Set DataDLws = Worksheets("DATA DOWNLOAD")
siteDataws_lastRow = siteDataws.Range("B65536").End(xlUp).Row
DataDLws_lastRow = DataDLws.Range("A65536").End(xlUp).Row

DataDLws.Range("R2:R" & DataDLws_lastRow & ", T2:T" & DataDLws_lastRow).Copy
siteDataws.Select
siteDataws.Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
DataDLws.Range("BJ2:BJ" & DataDLws_lastRow, "BK2:BK" & DataDLws_lastRow).Copy
siteDataws.Select
siteDataws.Range("F3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Range("C3").Select

For Each c In siteDataws.Range("B3:B" & siteDataws_lastRow).Cells
If c < "" And c = "!No_PRorFB_cdt" Then
If rngAll Is Nothing Then
Set rngAll = c
Else
Set rngAll = Union(rngAll, c)
End If
End If
Next c
If Not rngAll Is Nothing Then rngAll.EntireRow.Delete

i = 1
siteDataws_lastRow = siteDataws.Range("B65536").End(xlUp).Row
For Each c In siteDataws.Range("B3:B" & siteDataws_lastRow).Cells
If c < "" Then
c.Offset(0, -1) = i
i = i + 1
End If
Next c

Application.DisplayAlerts = False
End Sub

If c < "" And c = "!No_PRorFB_cdt" Then

This line of code contains a redundancy. If c = "!No_PRorFB", then it
necessarily is not equal to "", so you can get rid of the null string
test and just use

If c = "!No_PRorFB_cdt" Then

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com




On Mon, 15 Mar 2010 09:53:01 -0700, Ayo
wrote:

I have a button on my excel with the following code. I have to click on it
twice, each time, for it to delete rows that contains "!No_PRorFB_cdt" in
column B. Is there something I am doing wrong here?

Private Sub cmdBuildKML_Click()
Dim siteDataws As Worksheet, DataDLws As Worksheet
Dim siteDataws_lastRow As Long, DataDLws_lastRow As Long, i As Integer
Dim c As Range, rngAll As Range

Application.DisplayAlerts = False
Set siteDataws = Worksheets("GoogleEarth_SiteData")
Set DataDLws = Worksheets("DATA DOWNLOAD")
siteDataws_lastRow = siteDataws.Range("B65536").End(xlUp).Row
DataDLws_lastRow = DataDLws.Range("A65536").End(xlUp).Row

DataDLws.Range("R2:R" & DataDLws_lastRow & ", T2:T" & DataDLws_lastRow).Copy
siteDataws.Select
siteDataws.Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
DataDLws.Range("BJ2:BJ" & DataDLws_lastRow, "BK2:BK" & DataDLws_lastRow).Copy
siteDataws.Select
siteDataws.Range("F3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Range("C3").Select

For Each c In siteDataws.Range("B3:B" & siteDataws_lastRow).Cells
If c < "" And c = "!No_PRorFB_cdt" Then
If rngAll Is Nothing Then
Set rngAll = c
Else
Set rngAll = Union(rngAll, c)
End If
End If
Next c
If Not rngAll Is Nothing Then rngAll.EntireRow.Delete

i = 1
siteDataws_lastRow = siteDataws.Range("B65536").End(xlUp).Row
For Each c In siteDataws.Range("B3:B" & siteDataws_lastRow).Cells
If c < "" Then
c.Offset(0, -1) = i
i = i + 1
End If
Next c

Application.DisplayAlerts = False
End Sub