Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to shift the cell based on the location of other cell?
I have a workbook with the contact information showing in several rows, for
example in the first Column and first Row (A1) it shows the "Name" then on the second column it shows the "Address" on the B2 and "Phone Number" on C3 and so on. As a result for every contact information it occuplies several rows. How to write an vba code to clean up the information so that for each of the conatct information the information showing on each of the column is the same row number as the "Name" column and perhaps remove all the blank rows at the end of the process? Thanks. Example: Name Address Phone Fax John Doe 123 Street 123-456-789 456-444-7845 Jane Doe 369-456-7899 456 Street 236-456-4578 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to shift the cell based on the location of other cell?
If none of the entries are missing an element of the address
Sub ClearBlanks() Columns("A:D").Specialcells(xlBlanks).Delete End Sub -- Regards, Tom Ogilvy "Paul" wrote in message ... I have a workbook with the contact information showing in several rows, for example in the first Column and first Row (A1) it shows the "Name" then on the second column it shows the "Address" on the B2 and "Phone Number" on C3 and so on. As a result for every contact information it occuplies several rows. How to write an vba code to clean up the information so that for each of the conatct information the information showing on each of the column is the same row number as the "Name" column and perhaps remove all the blank rows at the end of the process? Thanks. Example: Name Address Phone Fax John Doe 123 Street 123-456-789 456-444-7845 Jane Doe 369-456-7899 456 Street 236-456-4578 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to shift the cell based on the location of other cell?
Thanks Tom:
Yes some contact may not have the "Address" and/or "Phone" and/or "Fax" as a result I don't think to delete all the "Blank" cells will work. "Tom Ogilvy" wrote in message ... If none of the entries are missing an element of the address Sub ClearBlanks() Columns("A:D").Specialcells(xlBlanks).Delete End Sub -- Regards, Tom Ogilvy "Paul" wrote in message ... I have a workbook with the contact information showing in several rows, for example in the first Column and first Row (A1) it shows the "Name" then on the second column it shows the "Address" on the B2 and "Phone Number" on C3 and so on. As a result for every contact information it occuplies several rows. How to write an vba code to clean up the information so that for each of the conatct information the information showing on each of the column is the same row number as the "Name" column and perhaps remove all the blank rows at the end of the process? Thanks. Example: Name Address Phone Fax John Doe 123 Street 123-456-789 456-444-7845 Jane Doe 369-456-7899 456 Street 236-456-4578 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to shift the cell based on the location of other cell?
Sub adjustData()
Dim rng As Range, i As Long Dim cell As Range Set rng = Range("A2", Cells(Rows.Count, 4) _ .End(xlUp)).SpecialCells(xlConstants) For Each cell In rng If cell.Column = 1 Then If cell.Offset(1, 1) = "" Then cell.Offset(0, 1).Formula = "=NA()" If cell.Offset(1, 2) = "" Then cell.Offset(0, 2).Formula = "=NA()" If cell.Offset(1, 3) = "" Then cell.Offset(0, 3).Formula = "=NA()" End If End If End If End If If cell.Column = 2 Then If cell.Offset(1, 1) = "" Then cell.Offset(0, 1).Formula = "=NA()" If cell.Offset(1, 2) = "" Then cell.Offset(0, 2).Formula = "=NA()" End If End If End If If cell.Column = 3 Then If cell.Offset(1, 1) = "" Then cell.Offset(0, 1).Formula = "=NA()" End If End If If cell.Column 2 Then If IsEmpty(cell.Offset(-1, -1)) Then cell.Offset(0, -1).Formula = "=NA()" If cell.Column 3 Then If IsEmpty(cell.Offset(-1, -2)) Then _ cell.Offset(0, -2).Formula = "=NA()" End If End If End If Next Columns("A:D").SpecialCells(xlBlanks).Delete Columns("A:D").SpecialCells(xlFormulas, _ xlErrors).ClearContents End Sub -- Regards, Tom Ogilvy "Paul" wrote in message ... Thanks Tom: Yes some contact may not have the "Address" and/or "Phone" and/or "Fax" as a result I don't think to delete all the "Blank" cells will work. "Tom Ogilvy" wrote in message ... If none of the entries are missing an element of the address Sub ClearBlanks() Columns("A:D").Specialcells(xlBlanks).Delete End Sub -- Regards, Tom Ogilvy "Paul" wrote in message ... I have a workbook with the contact information showing in several rows, for example in the first Column and first Row (A1) it shows the "Name" then on the second column it shows the "Address" on the B2 and "Phone Number" on C3 and so on. As a result for every contact information it occuplies several rows. How to write an vba code to clean up the information so that for each of the conatct information the information showing on each of the column is the same row number as the "Name" column and perhaps remove all the blank rows at the end of the process? Thanks. Example: Name Address Phone Fax John Doe 123 Street 123-456-789 456-444-7845 Jane Doe 369-456-7899 456 Street 236-456-4578 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Word cell containing Shift/Enter to Excel.Keep it as 1 cell | Excel Discussion (Misc queries) | |||
Show value from specific column based on location of active cell | Excel Discussion (Misc queries) | |||
How do anchor a file to a location so no one can shift it? | Excel Discussion (Misc queries) | |||
Average based on value, not cell location | Excel Programming | |||
Sum Based on Cell location in Column | Excel Programming |