S
Sean
Good morning,
I would like to use this code to step through an Excel worksheet and delete
rows where the value in column 'A' is NOT like ######. I can get it to do
one specific row, but I can't get the loop to work right. The problem
seems to be my CurrentCell and NextCell objects, I must not be defining them
correctly. I get an error saying ActiveX cannot create object. I would
appreciate any help. Thank you.
Sean
Private Sub btnXL_Click()
On Error GoTo Err_btnXL_Click
Dim Test As Integer
'Create an object for Excel
Set xlObject = CreateObject("excel.application")
'Make the Excel Application visible
xlObject.Visible = True
Set xlBook = xlObject.Workbooks.Add("U:\Batching Files\batchcontrol.xls")
'Activate worksheet, select the range.
xlObject.Worksheets("Sheet1").Activate
xlObject.ActiveSheet.Range("A2").Select
xlObject.ActiveSheet.Range("A2").Activate
Set currentCell = CreateObject("Excel.Cells")
Set nextcell = CreateObject("Excel.Cells")
Set currentCell = xlObject.ActiveSheet.Range("A2")
Do While Not IsEmpty(currentCell)
Test = xlObject.ActiveSheet.Range(currentCell).Value Like "######"
If Test = False Then
xlObject.ActiveSheet.Rows(currentCell).Delete
End If
Set nextcell = currentCell.Offset(1, 0)
Set currentCell = nextcell
Loop
On Error Resume Next
xlObject.UserControl = True
Exit_btnXL_Click:
Exit Sub
Err_btnXL_Click:
MsgBox Err.Description
Resume Exit_btnXL_Click
End Sub
I would like to use this code to step through an Excel worksheet and delete
rows where the value in column 'A' is NOT like ######. I can get it to do
one specific row, but I can't get the loop to work right. The problem
seems to be my CurrentCell and NextCell objects, I must not be defining them
correctly. I get an error saying ActiveX cannot create object. I would
appreciate any help. Thank you.
Sean
Private Sub btnXL_Click()
On Error GoTo Err_btnXL_Click
Dim Test As Integer
'Create an object for Excel
Set xlObject = CreateObject("excel.application")
'Make the Excel Application visible
xlObject.Visible = True
Set xlBook = xlObject.Workbooks.Add("U:\Batching Files\batchcontrol.xls")
'Activate worksheet, select the range.
xlObject.Worksheets("Sheet1").Activate
xlObject.ActiveSheet.Range("A2").Select
xlObject.ActiveSheet.Range("A2").Activate
Set currentCell = CreateObject("Excel.Cells")
Set nextcell = CreateObject("Excel.Cells")
Set currentCell = xlObject.ActiveSheet.Range("A2")
Do While Not IsEmpty(currentCell)
Test = xlObject.ActiveSheet.Range(currentCell).Value Like "######"
If Test = False Then
xlObject.ActiveSheet.Rows(currentCell).Delete
End If
Set nextcell = currentCell.Offset(1, 0)
Set currentCell = nextcell
Loop
On Error Resume Next
xlObject.UserControl = True
Exit_btnXL_Click:
Exit Sub
Err_btnXL_Click:
MsgBox Err.Description
Resume Exit_btnXL_Click
End Sub