How to delete rows in visual basic
 

Hello,
I would like to make a code in visual basic for deleting all rows except the
rows which have a value of 1 in collum D. Thanks for helping me!

How to delete rows in visual basic
 

Public Sub DeleteDuplicateRowsUsingAutofilter()
Dim cRows As Long
Dim rng As Range

'first, count the rows to operate on
cRows = Cells(Rows.Count, TestColumn).End(xlUp).Row

'apply the autofilter for al matching cells
Columns("D").AutoFilter Field:=1, Criteria1:=1, Operator:=xlAnd

'we now have only matching rows visible, so we can
'delete these matching rows
Set rng = Range("D2").Resize(cRows - 1)
On Error Resum Next
Set rng = rng.SpecialCells(xlCellTypeVisible).EntireRow.Dele te
On Error Goto 0
If Not rng Is Nothing Then rng.EntireRow.Delete

End Sub




--
__________________________________
HTH

Bob

"Ruben" wrote in message
...
Hello,
I would like to make a code in visual basic for deleting all rows except
the
rows which have a value of 1 in collum D. Thanks for helping me!

How to delete rows in visual basic
 

Hi Ruben

Sorry for question for question. Are you talking about Excel rows to be
deleted by VB? If could you breifly tell me how to write 1 on Range("A1") on
an Excel sheet using VB 6 not VBA?


"Bob Phillips" wrote:


Public Sub DeleteDuplicateRowsUsingAutofilter()
Dim cRows As Long
Dim rng As Range

'first, count the rows to operate on
cRows = Cells(Rows.Count, TestColumn).End(xlUp).Row

'apply the autofilter for al matching cells
Columns("D").AutoFilter Field:=1, Criteria1:=1, Operator:=xlAnd

'we now have only matching rows visible, so we can
'delete these matching rows
Set rng = Range("D2").Resize(cRows - 1)
On Error Resum Next
Set rng = rng.SpecialCells(xlCellTypeVisible).EntireRow.Dele te
On Error Goto 0
If Not rng Is Nothing Then rng.EntireRow.Delete

End Sub




--
__________________________________
HTH

Bob

"Ruben" wrote in message
...
Hello,
I would like to make a code in visual basic for deleting all rows except
the
rows which have a value of 1 in collum D. Thanks for helping me!

How to delete rows in visual basic
 

It is the same in VB as it does in VBA, you just need to get an application
object.

--
__________________________________
HTH

Bob

"Varne" wrote in message
...
Hi Ruben

Sorry for question for question. Are you talking about Excel rows to be
deleted by VB? If could you breifly tell me how to write 1 on Range("A1")
on
an Excel sheet using VB 6 not VBA?


"Bob Phillips" wrote:


Public Sub DeleteDuplicateRowsUsingAutofilter()
Dim cRows As Long
Dim rng As Range

'first, count the rows to operate on
cRows = Cells(Rows.Count, TestColumn).End(xlUp).Row

'apply the autofilter for al matching cells
Columns("D").AutoFilter Field:=1, Criteria1:=1, Operator:=xlAnd

'we now have only matching rows visible, so we can
'delete these matching rows
Set rng = Range("D2").Resize(cRows - 1)
On Error Resum Next
Set rng = rng.SpecialCells(xlCellTypeVisible).EntireRow.Dele te
On Error Goto 0
If Not rng Is Nothing Then rng.EntireRow.Delete

End Sub




--
__________________________________
HTH

Bob

"Ruben" wrote in message
...
Hello,
I would like to make a code in visual basic for deleting all rows
except
the
rows which have a value of 1 in collum D. Thanks for helping me!