Edit Address List.

shyam

shyam

Active Member
Joined
Jul 18, 2005
Messages
302
I have imported an address details as detailed below. It is only a sample which contains a list of nearly 300 address one below the other. The output is taken from another program

Excel Workbook
ABC
11E50007AAA
2FLAT NO.D-41, MAYUR BUILDING,
3SHRISTI, SECTOR III,
4MIRA ROAD EAST,DIST.THANE
5P.I.N. 401 107
62E50027BBB
7EKNATH PATIL CHAWL,
8GOGRESS WADI,PATHRLI ROAD
9DOMBIVILI EAST
10DIST THANE-421 201
113E50042CCC
12NO.398, EKADANTHA, 9TH MAIN,
1310 TH CROSS,
14PADMANABHA NAGAR
15BANGALORE-560 070
Sheet1


Can someone give an easy method whereby I can update / edit the list in excel in an user friendly manner whereby I can list the address or print them. The thing is I need to avoid retyping the entire address list in excel to save time.

Thanks n rgds
shyam
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
Try this macro

Code: 
Sub addr()
Dim ws1 As Worksheet, ws2 As Worksheet, LR As Long, i As Long, j As Long, k As Long
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets.Add
With ws1
    LR = .Range("C" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        If .Range("A" & i) <> "" Then
            j = j + 1
            k = 1
        Else
            k = k + 1
        End If
        ws2.Cells(j, k).Value = .Range("C" & i).Value
    Next i
End With
ws2.Columns("A:G").AutoFit
End Sub
 
Upvote 0
Dear Mr. Vog,

Your macro is working perfectly. But I need to take into consideration the code also in "B" column. Can you advise where syntax needs to be changed to include column B so that the output includes code in first column followed by the address in subsequent columns.

Tks n rgds
shyam
 
Upvote 0
Try this:

Code: 
Sub addr()
Dim ws1 As Worksheet, ws2 As Worksheet, LR As Long, i As Long, j As Long, k As Long
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets.Add
With ws1
    LR = .Range("C" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        If .Range("A" & i) <> "" Then
            j = j + 1
            ws2.Cells(j, 1).Value = .Range("B" & i).Value
            k = 2
        Else
            k = k + 1
        End If
        ws2.Cells(j, k).Value = .Range("C" & i).Value
    Next i
End With
ws2.Columns("A:G").AutoFit
End Sub
 
Upvote 0
Perfect Mr. Vog. Thanks a lot for your help.

One more query, if you don't mind..

in case if I need to take into account any extra columns, then what syntax do i change and where?

Again Thanks a lot for your help. It has saved my time to a very great extent.

Rgds
Shyam
 
Upvote 0
Is this what you mean? With a couple of extra columns:

Excel Workbook
ABCDE
11E50007AABBAAA
2FLAT NO.D-41, MAYUR BUILDING,
3SHRISTI, SECTOR III,
4MIRA ROAD EAST,DIST.THANE
5P.I.N. 401 107
62E50027CCDDBBB
7EKNATH PATIL CHAWL,
8GOGRESS WADI,PATHRLI ROAD
9DOMBIVILI EAST
10DIST THANE-421 201
113E50042EEFFCCC
12NO.398, EKADANTHA, 9TH MAIN,
1310 TH CROSS,
14PADMANABHA NAGAR
15BANGALORE-560 070
Sheet1



Code: 
Sub addr()
Dim ws1 As Worksheet, ws2 As Worksheet, LR As Long, i As Long, j As Long, k As Long
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets.Add
With ws1
    LR = .Range("E" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        If .Range("A" & i) <> "" Then
            j = j + 1
            ws2.Cells(j, 1).Value = .Range("B" & i).Value
            ws2.Cells(j, 2).Value = .Range("C" & i).Value
            ws2.Cells(j, 3).Value = .Range("D" & i).Value
            k = 4
        Else
            k = k + 1
        End If
        ws2.Cells(j, k).Value = .Range("E" & i).Value
    Next i
End With
ws2.Columns("A:Z").AutoFit
End Sub

Result:

Excel Workbook
ABCDEFGH
1E50007AABBAAAFLAT NO.D-41, MAYUR BUILDING,SHRISTI, SECTOR III,MIRA ROAD EAST,DIST.THANEP.I.N. 401 107
2E50027CCDDBBBEKNATH PATIL CHAWL,GOGRESS WADI,PATHRLI ROADDOMBIVILI EASTDIST THANE-421 201
3E50042EEFFCCCNO.398, EKADANTHA, 9TH MAIN,10 TH CROSS,PADMANABHA NAGARBANGALORE-560 070
Sheet6
 
Upvote 0
Mr. Vog,

If you don't mind and if time permits, can you explain the meaning of each of the main syntax of your code. This can help in modifying the syntax of the code wherever necessary, depending on the structure of the statement.

Thanking you once again for the help.

Rgds
shyam
 
Upvote 0
Here is the code with comments. This code is quite specific to the structure of your data. There may be better ways of coding this as well.

Code: 
Sub addr()
'Declare variables
Dim ws1 As Worksheet, ws2 As Worksheet, LR As Long, i As Long, j As Long, k As Long
'Assign ws1 as Sheet1
Set ws1 = Sheets("Sheet1")
'Add a sheet and assign it to ws2
Set ws2 = Sheets.Add
'Anything inside With / End With starting with a . like .Range("A" & i) refers to ws1
With ws1
'Find the last used row in column E (the longest column)
    LR = .Range("E" & Rows.Count).End(xlUp).Row
'Loop from row 1 to the last row
    For i = 1 To LR
'If the value in column A isn't blank, this is a new address
        If .Range("A" & i) <> "" Then
'Increment j so that we write to the next row in ws2
            j = j + 1
'Write the values from ws1 columns B:D to ws2 columns A:C (this could be coded better but I just adapted my previous code)
            ws2.Cells(j, 1).Value = .Range("B" & i).Value
            ws2.Cells(j, 2).Value = .Range("C" & i).Value
            ws2.Cells(j, 3).Value = .Range("D" & i).Value
'Next empty column in ws2 is 4 (D)
            k = 4
        Else
'ws1 col A is blank so this is the next part of the same address: increment the column counter for ws2
            k = k + 1
        End If
'write the next part of the address to the next blank column in ws2
        ws2.Cells(j, k).Value = .Range("E" & i).Value
    Next i 'Loop
End With
'Autosize the columns in ws2
ws2.Columns("A:Z").AutoFit
End Sub
 
Upvote 0
Thanks Mr. Vog.

I will try it out with some modification and will revert back to you about it.

Tks n rgds

Shyam
 
Upvote 0
Could anybody help me modify VoG's macro so that it captures all the columns of the rows between A values and not just "E"? Well maybe not all the colums but B:K or show me how I can adjust it?

Thanks,
Jason
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,214,659
Messages
6,120,783
Members
448,992
Latest member
prabhuk279

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top