CODEWITHSUNDEEP
vbaexcelexcel-2010
Pedro
Pedro

Reputation: 331

How to make a sub that stops when it finds a blank cell?

I was working on a database in Excell 2010 and found many duplicated names. I tried to make a VBA Script to solve this, but it just ends on a infinite loop. I wanted it to stop when it hits a blank cell. Heres the code I came up with

Sub excluirDuplicatas()

   Dim lngCont As Long
   Dim lngLinhas As Long
   Dim rngNom1 As Range
   Dim rngNom2 As Range

lngCont = 0
lngLinhas = Cells.SpecialCells(xlCellTypeLastCell).Row

While lngCont <= lngLinhas

Set rngNom1 = ActiveCell
Set rngNom2 = rngNom1.Offset(1, 0)

While rngNom1.Value = rngNom2.Value And rngNom2 <> lastRow


rngNom2.Select
rngNom2.EntireRow.Delete
Set rngNom2 = ActiveCell
Wend


Set rngNom1 = rngNom2
Set rngNom1 = ActiveCell



Set rngNom1 = rngNom2
rngNom1.Select

lngCont = lngCont + 1

Wend

End Sub

I don't know whats wrong, my guess is the part where I state

rngNom2 <> Cells.SpecialCells(xlCellTypeLastCell).Row

Please be aware that I starded VBA Programing today(March 22th). If you could please post not only the answer but also a quick explanation I would appreciate.

PS: The data sheet has over 2000 lines long

Upvotes: 0

Views: 1976

Answers (3)

David Zemens
David Zemens

Reputation: 53663

Well I'm not 100% sure what you're doing, but this would be a start.

Start by declaring a range variable rng taht contains all the cells you want to examine. The way I have set this rng variable automatically stops at the first blank cell in the column.

We will use the counter r to iterate over these cells, from last to first (this is necessary when deleting).

We will use a variable cl as a single cell range, during this iteration.

Sub excluirDuplicatas()

Dim rng as Range
Dim r as Integer 'row counter.
Dim cl As Range


lngCont = 0
lngLinhas = Cells.SpecialCells(xlCellTypeLastCell).Row

set rng = Range(ActiveCell.Address, Range(ActiveCell.Address).End(xlDown)) 

For r = rng.Rows.Count to 1 Step - 1 'Step backwards when deleting, otherwise you have infinite loop

    'This tests to see if the cell value is the same as the value below it.
     ' and deletes it if it is the same value.
    If cl.Value = cl.Offset(1,0).Value Then
        cl.EntireRow.Delete

    End If
Next

End Sub

Upvotes: 0

user2200765
user2200765

Reputation: 21

I think this command will help you. Replace Range("a1") with whatever range you want to test.

IsEmpty(Range("a1"))

ie, do while isempty(range("a1"))... or do while not isempty(range("a1"))

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149335

I would recommend using a For Loop so that you can avoid using .Select You might want to see THIS LINK

So for example your code will look something like this

For i = 1 To lastRow
    If Len(Trim(Sheets("Sheet1").Range("A" & i).Value)) = 0 Then Exit For

    '
    '~~> Rest of your code
    '
Next i

Upvotes: 2

Related Questions