programing excel with visual basic?

Question

i'm trying to make a little "program" in excel worksheets with visual basic i took a course but it wasn't very advanced so i got some doubts.

So...i have two worksheets "2" and "3" , i've got data in in both sheets in the same column which is B, I want for both of them to compare each other and eliminate the row in which the data is repeated in the second worksheet, i tried this:

Sub sas()

For Each c In Sheet3.Range("b7:b1000")
If c.Value = Sheet2.Range("b7:1000") Then
c.Value = Selection.EntireRow.Delete
End If
Next c

End Sub

its wrong so i dont know what else to use its pretty easy but as i told u my class wasnt very advanced

THANK YOU in advanced

Answer 1

Here is one way to do as you wish. When deleting rows in a macro loop, you must start from the bottom row and evaluate up. Otherwise, when when a row is deleted Excel's renumbering process causes rows to be skipped and not evaluated.



This assumes that you want to only delete the duplicate entries and retain one entry, not delete both entries.



Sub DeleteDups()

Dim i

Sheets("Sheet3").Activate

For i = 1000 To 7 Step -1

If Application.CountIf(Sheets("Sheet2"). Range("B7:B1000"), _

Cells(i, "B")) > 0 Then

Cells(i, "B").EntireRow.Delete

End If

Next

End Sub

Answer 2

Your main problem is trying to operate on a whole range, and using selection with no selected cells. My code is just mimics yours.



The fastest way should be performing a Find, rather than testing each cell. Try recording a macro where you select cells, do a find, and delete the row. Then you'll have half the code needed.



Public Sub sas()

Dim r3 As Range, r2 As Range

retest:

For Each r3 In Sheet3.Range("b7:b10")

For Each r2 In Sheet2.Range("b7:b10")

If r3.Text = r2.Text Then Call r3.Delete(xlShiftUp): GoTo retest

Next r2

Next r3

End Sub

Answer 3

Microsoft seen hassle-free exhibit is an IDE for arising living house windows courses utilising the seen hassle-free programming language. it particularly is lots extra extensive than seen hassle-free for Excel or get admission to, which specially in basic terms is for making macros to do stuff straight away on your Excel and get admission to archives.