Hello,

I am new to VB and perhaps what I am trying to do doesn't require a
macro, although I would prefer it. I have tried to do this myself by
copying and pasting code from multiple sources but am having no luck.

I would like a macro that does the following:

1) prompts the user to input a range of values (this is a column on
another worksheet containing row numbers that survived a filtering
procedure)
2) compares the user inputted numbers with the row numbers in another
sheet
3) keep all rows whose row number matches one/any of the values given
by the user and delete all the rest
4) output surviving rows to a new sheet

I apologize if this has been asked before but I couldn't find anything
(brain is a little tired).

Thanks alot!

Brett

The following is along the lines of what I am thinking, minus the
putting of the rows into a new sheet deal (which is not absolutely
essential for me).

Sub DelUnmatchedRows()
Dim rRange As Range
Dim i&
Rng = Selection.Rows.Count

On Error Resume Next
Set rRange = Application.InputBox(Prompt:= _
"Please select a range with your Mouse.", _
Title:="SPECIFY RANGE", Type:=8)


If (rRange Is Nothing) = False Then


ActiveCell.Offset(0, 0).Select
Application.ScreenUpdating = False
For i = 1 To Rng
For Each rngCell In rRange.Cells
If (ActiveCell.Value Eqv rngCell.Value) = False Then
Selection.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Next i
Application.ScreenUpdating = True
End Sub

I am getting the invalid next control variable error and the editor is
highlighting i. Thanks again
Brett

Hi Brett,

Try the following. You were missing the next and end if. Indent code in
for/next loops and If/else/endif like I have done and you will be able to
follow the code more easily and see where these are missing.

Also Rng = Selection.Rows.Count as in the wrong place in the code.

I don't know if it does what you want. It is untested.

Sub DelUnmatchedRows()
Dim rRange As Range
Dim i As Long
Dim Rng As Long

On Error Resume Next
Set rRange = Application.InputBox(Prompt:= _
"Please select a range with your Mouse.", _
Title:="SPECIFY RANGE", Type:=8)
On Error GoTo 0

'This needs to be after you select the range
Rng = Selection.Rows.Count

If Not rRange Is Nothing Then
'The next line Offset(0, 0) does nothing
'needs value for row or column or both
ActiveCell.Offset(0, 0).Select
Application.ScreenUpdating = False
For i = 1 To Rng
For Each rngcell In rRange.Cells
If (ActiveCell.Value Eqv rngcell.Value) = False Then
Selection.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Next rngcell
Next i
End If

Application.ScreenUpdating = True
End Sub


Regards,

OssieMac

Thanks OssieMac! I am still new to this and I am beginning to
recognize how important proper formatting is to debugging etc.

However, leaving the above code 'as is', I still don't get any row
deletion.
This is how I envision the macro to work:
First, I highlight a column on the spreadsheet
Next, I am prompted to select a range (another column on the sheet)
Then, the macro compares the two columns and any values that are not
common are deleted.

The macro compiles fine but as of right now it doesn't do anything!
Does the value of ActiveCell change when the next range is
highlighted, therefore I am comparing the same thing?
Thanks,
Brett

Hi Brett,

I knew that the macro did not do anything and it appears that my assumption
that you were just trying to get started was correct.

I'll write some code to do what you want and include comments so that you
can understand what it is all doing.

Do you want some validation to ensure that entire columns are selected and
not just part of a column?

Also, do you want a confirmation message to allow user to proceed after the
selections are made because when deleting data, it can be hard to get back if
an error is made with the selection?

Is my assumption correct in that you want to delete the entire row if the
values do not match so that it it removed from both columns?

Regards,

OssieMac



"bengalengel" wrote:

Thanks OssieMac! I am still new to this and I am beginning to
recognize how important proper formatting is to debugging etc.

However, leaving the above code 'as is', I still don't get any row
deletion.
This is how I envision the macro to work:
First, I highlight a column on the spreadsheet
Next, I am prompted to select a range (another column on the sheet)
Then, the macro compares the two columns and any values that are not
common are deleted.

The macro compiles fine but as of right now it doesn't do anything!
Does the value of ActiveCell change when the next range is
highlighted, therefore I am comparing the same thing?
Thanks,
Brett

Hi OssieMac,
Yes the validation and confirmation prompts would be helpful and yes I
do want to delete the entire row if the value in that row's column is
not equal to ANY of the values in the 2nd column. The way my data is
set up right now I have 1000 rows above 100 other rows. I want to see
if the value in column A of the first 1000 rows matches ANY of the
values in the first column of the second 100 rows. If a row has a
matching value in the second column its a keeper, if not its a gonner.
Thanks alot! and please let me know if I am being clear.
Brett