How to delete rows in visual basic

R

Ruben

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!
 
B

Bob Phillips

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.Delete
On Error Goto 0
If Not rng Is Nothing Then rng.EntireRow.Delete

End Sub
 
V

Varne

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? [email protected]
 
B

Bob Phillips

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