Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Clairification in finding and deleting cells from one sheet to ano
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Clairification in finding and deleting cells from one sheet to ano
On 19 Mar, 21:23, Johnny B wrote:
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 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....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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
finding and deleting cells from one excel sheet to another | Excel Discussion (Misc queries) | |||
Finding Number of cells in a column of other sheet having a specific word in them | New Users to Excel | |||
Finding, searching, and comparing cells to another sheet | Excel Worksheet Functions | |||
Finding and Deleting | New Users to Excel | |||
Finding/deleting duplicates and merging cells | Excel Worksheet Functions |