ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,232
- Office Version
- 2007
- Platform
- Windows
Morning,
Can you advise please.
My current database is A2:Y34
I click into cell A35 and start to enter data along that row.
I notice the row doesnt have any borders around each cell at present.
I run the code below which sorts A-Z and what ive typed into row A35 in now placed correctly within my database.
This still leaves row A35 without anyboders,this happens for each row i add data on & continues down the list
Basically i would like to add these borders so it then matches the database.
I am not sure where i need to put the code for this.
.
Can you advise please.
My current database is A2:Y34
I click into cell A35 and start to enter data along that row.
I notice the row doesnt have any borders around each cell at present.
I run the code below which sorts A-Z and what ive typed into row A35 in now placed correctly within my database.
This still leaves row A35 without anyboders,this happens for each row i add data on & continues down the list
Basically i would like to add these borders so it then matches the database.
I am not sure where i need to put the code for this.
.
Rich (BB code):
Private Sub CommandButton1_Click()
Dim InSheet As Worksheet
Set InSheet = ThisWorkbook.Worksheets("A to Z")
Dim LastRow As Integer
LastRow = InSheet.Cells(Rows.Count, 58).End(xlUp).Row
With InSheet.Sort ' sort data from A to Z
.SetRange InSheet.Range("A2:Y" & LastRow)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Private Sub SortList_Click()
Dim oLo As ListObject
Dim oLc1 As ListColumn, oLc2 As ListColumn
Dim i As Integer, x As Integer
Dim splitThis As String, alpha As String, num As String
Application.ScreenUpdating = False
'setup what/where to workwith
Set oLo = ActiveWorkbook.Worksheets("INFO").ListObjects("Table20")
'add 2 columns "Alpha" and "Num"
With oLo
Set oLc1 = .ListColumns.Add
oLc1.Name = "Alpha"
Set oLc2 = .ListColumns.Add
oLc2.Name = "Num"
'populate the new columns
For x = 1 To oLo.DataBodyRange.Rows.Count
splitThis = .ListColumns("I CODE KEY TYPE").DataBodyRange.Rows(x)
alpha = "": num = ""
For i = 1 To Len(splitThis)
If IsNumeric(Mid(splitThis, i, 1)) Then
num = num & Mid(splitThis, i, 1)
Else
alpha = alpha & Mid(splitThis, i, 1)
End If
Next i
.ListColumns("Alpha").DataBodyRange.Rows(x) = alpha
.ListColumns("Num").DataBodyRange.Rows(x) = num
Next x
'sort
With .Sort
.SortFields.Clear
.SortFields.Add Key:=ActiveWorkbook.Worksheets("INFO").Range("Table20[Alpha]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=ActiveWorkbook.Worksheets("INFO").Range("Table20[Num]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'remove the 2 added columns
.ListColumns("Num").Delete
.ListColumns("Alpha").Delete
End With
Application.ScreenUpdating = True
End Sub