Clairification in finding and deleting cells from one sheet to ano

G

Guest

I am unfamiliar with Excel, and I was giving these instructions in "finding
and deleting cells from one excel sheet to another" by an expert Excel user.
However, i dont understand them. I was hoping somoene could put these
instructions in lamens terms, so i can understand them.

" O.K. the try this.

It inserts a column to left of data on both sheets and concatonates all data
in to one column and then looks it up in the second sheet.

Dim rngeSht1 As Range
Dim rngeSht2 As Range
Dim ClientName
Dim Addr1
Dim City
Dim State
Dim c
Dim NameToFind
Dim Y

Sub Delete_Rows()

Sheets("Sheet1").Select

'Insert a column to left of data on sheet 1
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select

'Set this to a range as column 1 and to include all rows
Set rngeSht1 = Worksheets("Sheet1").Range("A1", Cells(Rows.Count, 1))

'Concatonate all the values in cells and place in one cell
'Each value trimmed of superflourous leading and trailing spaces
For Each c In rngeSht1
ClientName = Trim(c.Offset(0, 1).Range("A1"))
Addr1 = Trim(c.Offset(0, 2).Range("A1"))
City = Trim(c.Offset(0, 3).Range("A1"))
State = Trim(c.Offset(0, 4).Range("A1"))

c.Value = ClientName & Addr1 & City & State

If c.Value = "" Then
Exit For 'Exit when run out of data
End If
Next c


Sheets("Sheet2").Select

'Insert a column to left of data on sheet 2
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select

'Set this to a range as column 1 and to include all rows
Set rngeSht2 = Worksheets("Sheet2").Range("A1", Cells(Rows.Count, 1))

'Concatonate all the values in cells and place in one cell
'Each value trimmed of superflourous leading and trailing spaces
For Each c In rngeSht2
ClientName = Trim(c.Offset(0, 1).Range("A1"))
Addr1 = Trim(c.Offset(0, 2).Range("A1"))
City = Trim(c.Offset(0, 3).Range("A1"))
State = Trim(c.Offset(0, 4).Range("A1"))

c.Value = ClientName & Addr1 & City & State

If c.Value = "" Then
Exit For 'Exit when run out of data
End If
Next c

'For each value in sheet 1, find corresponding value
'in sheet 2 and if found, delete entirerow.
For Each c In rngeSht1
If c.Value = "" Then
Exit For 'Exit when run out of data to find
End If

NameToFind = c.Value
Set Y = rngeSht2.Find(What:=NameToFind, _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns _
, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

If Not Y Is Nothing Then 'Y Not Nothing = Found target
Do
Y.EntireRow.Delete
'NOTE: FindNext does not work when a row from the range
'has been deleted. Must repeat full find method
Set Y = rngeSht2.Find(What:=NameToFind, _
LookIn:=xlFormulas, LookAt:=xlWhole,
SearchOrder:=xlByColumns _
, SearchDirection:=xlNext, MatchCase:=False,
SearchFormat:=False)
Loop While Not Y Is Nothing
End If

Next c
Sheets("Sheet1").Select
Columns("A:A").Delete
Range("A1").Select
Sheets("Sheet2").Select
Columns("A:A").Delete
Range("A1").Select "

THANK YOU SO MUCH!!

~Johnny B
 
L

loudfish

I am unfamiliar with Excel, and I was giving these instructions in "finding
and deleting cells from one excel sheet to another" by an expert Excel user.
However, i dont understand them. I was hoping somoene could put these
instructions in lamens terms, so i can understand them.

" O.K. the try this.

It inserts a column to left of data on both sheets and concatonates all data
in to one column and then looks it up in the second sheet.

Dim rngeSht1 As Range
Dim rngeSht2 As Range
Dim ClientName
Dim Addr1
Dim City
Dim State
Dim c
Dim NameToFind
Dim Y

Sub Delete_Rows()

Sheets("Sheet1").Select

'Insert a column to left of data on sheet 1
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select

'Set this to a range as column 1 and to include all rows
Set rngeSht1 = Worksheets("Sheet1").Range("A1", Cells(Rows.Count, 1))

'Concatonate all the values in cells and place in one cell
'Each value trimmed of superflourous leading and trailing spaces
For Each c In rngeSht1
ClientName = Trim(c.Offset(0, 1).Range("A1"))
Addr1 = Trim(c.Offset(0, 2).Range("A1"))
City = Trim(c.Offset(0, 3).Range("A1"))
State = Trim(c.Offset(0, 4).Range("A1"))

c.Value = ClientName & Addr1 & City & State

If c.Value = "" Then
Exit For 'Exit when run out of data
End If
Next c

Sheets("Sheet2").Select

'Insert a column to left of data on sheet 2
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select

'Set this to a range as column 1 and to include all rows
Set rngeSht2 = Worksheets("Sheet2").Range("A1", Cells(Rows.Count, 1))

'Concatonate all the values in cells and place in one cell
'Each value trimmed of superflourous leading and trailing spaces
For Each c In rngeSht2
ClientName = Trim(c.Offset(0, 1).Range("A1"))
Addr1 = Trim(c.Offset(0, 2).Range("A1"))
City = Trim(c.Offset(0, 3).Range("A1"))
State = Trim(c.Offset(0, 4).Range("A1"))

c.Value = ClientName & Addr1 & City & State

If c.Value = "" Then
Exit For 'Exit when run out of data
End If
Next c

'For each value in sheet 1, find corresponding value
'in sheet 2 and if found, delete entirerow.
For Each c In rngeSht1
If c.Value = "" Then
Exit For 'Exit when run out of data to find
End If

NameToFind = c.Value
Set Y = rngeSht2.Find(What:=NameToFind, _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns _
, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

If Not Y Is Nothing Then 'Y Not Nothing = Found target
Do
Y.EntireRow.Delete
'NOTE: FindNext does not work when a row from the range
'has been deleted. Must repeat full find method
Set Y = rngeSht2.Find(What:=NameToFind, _
LookIn:=xlFormulas, LookAt:=xlWhole,
SearchOrder:=xlByColumns _
, SearchDirection:=xlNext, MatchCase:=False,
SearchFormat:=False)
Loop While Not Y Is Nothing
End If

Next c
Sheets("Sheet1").Select
Columns("A:A").Delete
Range("A1").Select
Sheets("Sheet2").Select
Columns("A:A").Delete
Range("A1").Select "

THANK YOU SO MUCH!!

~Johnny B
Click to expand...

It's better is you repost the question in the original thread, so that
people understand the context of your question:

I suggest you have a look at this page:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Particularly:
http://www.mvps.org/dmcritchie/excel/getstarted.htm#havemacro

If you really want to understand the code, you'll need to get to grips
with the info on that page.

If you just want a solution to your problem, I suggest that you use a
combination of CONCATENATE and VLOOKUP to find items which are on both
pages.
CONCATENATE - to create a single listing (ie combine all the
information into a single column). Do this for both list a and list b.
VLOOKUP - for each item in list a, look to see if it exists in list b.

On the Menu, Insert....Function.... and type in CONCATENATE, and it'll
help you build the right formula.

HTH

Andrew
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Deleting rows with 11+ characters. 4
Sheet to sheet code Type Mismatch 6
Value from one sheet paste under same value on other sheet. 7
macro add 10
Copy row info but not in normal column sequence 6
Filter and copy data to other sheet 3
Array with many cells from a row but not in order or together 11
Excel How to run multiple Private Sub Worksheet_Change(ByVal Target As Range) 0

Top