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

Hi Brett,

My interpretation is now different to what I originally interpretted. I
think that I understand but an example is like a picture (worth a 1000 words)
and I would like to confirm that we are on the same wavelength. Can you give
me an example of worksheet name and actual range of the 1000 and the same for
the 100. (Just for the one column in each that is being compared.)

Regards,

OssieMac

"bengalengel" wrote:

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

Hi again Brett,

Have another question. Which range are you selecting before invoking the
macro to get the second range? (That is the 1000 or 100).

Regards,

OssieMac

"bengalengel" wrote:

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

Hi again Brett,

Didnt wait for your answer. Decided to run with what I interpreted as your
requirements and give it to you and if not right then I can modify it.

Firstly ensure that you have a backup of your data in case the macro does
not do what you expect.

I have written the macro so that instead of initially deleting the rows, it
will colour the rows yellow. That will give you the opportunity to check a
reasonable sample to ensure it is going to delete the correct rows. When you
are satisfied that it is correct, simply comment out the colorindex line and
remove the comment (single quote) from the delete line.

Before running the macro, you will need to select the 1000 or so data from
the first to last cell of the range required. (Not the entire column).

At the Input box prompt, select the second range of 100 or so. (If you
wanted to select the other way around then I can change it later but run with
this first and test if it achieves what you want to do.)

Processing will terminate if you Cancel in the Input box.

I tried to keep the code as simple as possible to give you the opportunity
to try to understand what it is all doing. Feel free to ask questions about
any of it that you do not understand and I will try to explain.

If the macro does not do what you want then please answer the questions on
my previous 2 posts and I will attempt to correct it.

Sub DelUnmatchedRows()
Dim rng1 As Range 'Initial selection
Dim rng2 As Range 'InputBox selection entry
Dim i As Long 'In For/Next Loop
Dim foundcell As Range 'Returned in Find function

'Your Selection prior to running macro
'Assign selection to a variable
Set rng1 = Selection

'Test that selection is minimun 2 cells
'and only one column wide. (Not multiple columns)
If rng1.Cells.Count < 2 Or rng1.Columns.Count < 1 Then
MsgBox "Invalid range selection." & Chr(13) & _
"Select multiple cells in one column only" _
& Chr(13) & "Processing will terminate"
Exit Sub
End If

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

'If user cancels in the Input Box
If rng2 Is Nothing Then
MsgBox "User cancelled" _
& Chr(13) & "Processing will terminate"
Exit Sub
End If

Application.ScreenUpdating = False
'Test that selection is minimun 2 cells
'and only one column wide.
If rng2.Cells.Count < 2 Or rng2.Columns.Count < 1 Then
MsgBox "Invalid range selection." & Chr(13) & _
"Select multiple cells in one column only" _
& Chr(13) & "Processing will terminate"
Exit Sub
End If

'Loop through each cell in rng1 and
'find the value in rng2.
'When deleting rows, must work backwards
'from last row. Otherwise adjacent rows
'do not delete.

For i = rng1.Rows.Count To 1 Step -1
Set foundcell = rng2. _
Find(What:=rng1.Cells(i, 1).Value, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

'If not found in rng2 then delete row
If foundcell Is Nothing Then
'After testing, comment out colorindex line
'and remove comment from delete line.
rng1.Cells(i, 1).EntireRow.Interior.ColorIndex = 6
'rng1.Cells(i, 1).EntireRow.Delete
End If
Next i
Application.Goto Range("A1"), scroll:=True
Application.ScreenUpdating = True
End Sub


Regards,

OssieMac

Hi OssieMac,
below is an example of the spreadsheet layout I am dealing with. I
want to compare the first column in the chunk of data containing a
full row (A5:14) with the column below this data with header 'row'
B18:28. If it would make it easier, the first column (A5:14) is
actually = to the row number. So, In this example I would highlight
A5:14 (representing the 1000), then invoke macro, I would be prompted
to highlight another range (B18:28 - representing 100), the values
would be compared. In this example all I would be left with is the row
with a '5' in the first column. I hope this is what you are looking
for.
Thanks,
Brett






5 U48705 11.57277673 11.48299326 11.49817988 11.39181932 11.51182406
11.72478724
6 M87338 8.903613038 8.931832415 8.903477047 9.104027145 9.197445184
9.071895683
7 X51757 7.889470277 7.680604745 7.6813734 7.599011979 7.64599037
7.730412447
8 X69699 8.598157828 8.818287912 8.952038751 8.81242113 8.909250938
9.267898962
9 L36861 3.629816252 3.713987347 3.604110976 3.720667264 3.710584461
3.798856123
10 L13852 6.280349023 6.321024974 6.439949032 6.380803877 6.56458805
6.765243354
11 X55005 6.035178802 6.065392297 6.16608982 5.973988714 6.13477326
6.561941301
12 X79510 6.665200269 6.321051756 6.374943774 6.528711441 6.441226071
6.440122473
13 M21121 3.827989034 4.111530913 3.8813984 4.179986423 4.062780619
4.389922903
14 J02843 4.190077627 4.187559742 4.306467935 4.303607206 4.328122558
4.292004354


Row
5
156
200
209
80
500
509
565
990
915
45

OK brett it looks like we both posted about the same time. I tested the macro
against the data you gave me and there is one change to make.

In the following line in the find block of code:-

LookAt:=xlPart, _

Change this to:-

LookAt:=xlWhole, _


Your example is great. It's a pity that examples break up so much when
posted but I was able to extract the data OK. I now think that with the above
modification the macro should do what you want.

Regards,

OssieMac

OssieMac,

Thank you so much! You are fantastic, it works very nicely. It is a
little slow because my main file is 54,000 rows long but I can deal
with it. Thanks again, this macro saves me so much time.

Brett