Hello everyone:
At the moment I have 3 statements that i would like to streamline and combine into one. I would like to just press commandbutton1() and do the whole process rather than having 3 steps. Can I combine or I am better off doing it the way it is now?
Code:
Private Sub CommandButton1_Click()
'Prepare exports
'Delete cells and find and replace Y
Sheets("byemployee").Select
Rows("1:7").Select
Selection.Delete Shift:=xlUp
Cells.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheets("byposition").Select
Rows("1:7").Select
Selection.Delete Shift:=xlUp
Columns("E:E").Select
Selection.Copy
Columns("A:A").Select
ActiveSheet.Paste
Cells.Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Columns("I:I").Select
Selection.Replace What:="$", Replacement:="N", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("I:I").Select
Selection.Replace What:="", Replacement:="Y", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
MsgBox "Step 1 Completed"
End Sub
____________________________________________
Private Sub CheckBox7_Click()
'delete columns that are not referenced
Dim k, I As Long
With Sheets("byposition")
k = .UsedRange.Columns.Count
For I = k To 1 Step -1
Select Case LCase$(.UsedRange.Cells(1, I))
Case "job code", "functional area", "position title", "currency", "hourly base min", "hourly base mid", "hourly base max", _
"hourly mkt - 10th", "hourly mkt - 25th", "hourly mkt - 50th", "hourly mkt - 75th", "hourly mkt - 90th", "hourly at target", _
"mid to hourly target delta | %", "#ees", "annual base min", "annual base mid", "annual base max", "salary at target", _
"market percentile of salary mid", "annualized base min", "annualized base mid", "annualized base max", "annu. base mkt - 10th", _
"annu. base mkt - 25th", "annu. base mkt - 50th", "annu. base mkt - 75th", "annu. base mkt - 90th", "annu. base at target", "mid to annu. target delta | %"
'do nothing
Case Else
.UsedRange.Columns(I).Delete
End Select
Next I
End With
End Sub
________________________________________
Private Sub CheckBox8_Click()
'delete columns that are not referenced
Dim k, I As Long
With Sheets("byemployee")
k = .UsedRange.Columns.Count
For I = k To 1 Step -1
Select Case LCase$(.UsedRange.Cells(1, I))
Case "employee id", "position title", "job code", "employee name", "employee dept", "salary", "market percentile of base salary", _
"hourly rate", "market percentile of hourly rate", "annualized base pay", "market percentile of annu. base", "annual base min", _
"annual base mid", "annual base max", "salary compa-ratio", "salary range penetration", "hourly base min", "hourly base mid", _
"hourly base max", "hourly compa-ratio", "hourly range penetration", "annualized base min", "annualized base mid", "annualized base max", _
"annualized compa-ratio", "annualized range penetration", "target market-ratio"
'do nothing
Case Else
.UsedRange.Columns(I).Delete
End Select
Next I
End With
End Sub
At the moment I have 3 statements that i would like to streamline and combine into one. I would like to just press commandbutton1() and do the whole process rather than having 3 steps. Can I combine or I am better off doing it the way it is now?
Code:
Private Sub CommandButton1_Click()
'Prepare exports
'Delete cells and find and replace Y
Sheets("byemployee").Select
Rows("1:7").Select
Selection.Delete Shift:=xlUp
Cells.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheets("byposition").Select
Rows("1:7").Select
Selection.Delete Shift:=xlUp
Columns("E:E").Select
Selection.Copy
Columns("A:A").Select
ActiveSheet.Paste
Cells.Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Columns("I:I").Select
Selection.Replace What:="$", Replacement:="N", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("I:I").Select
Selection.Replace What:="", Replacement:="Y", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
MsgBox "Step 1 Completed"
End Sub
____________________________________________
Private Sub CheckBox7_Click()
'delete columns that are not referenced
Dim k, I As Long
With Sheets("byposition")
k = .UsedRange.Columns.Count
For I = k To 1 Step -1
Select Case LCase$(.UsedRange.Cells(1, I))
Case "job code", "functional area", "position title", "currency", "hourly base min", "hourly base mid", "hourly base max", _
"hourly mkt - 10th", "hourly mkt - 25th", "hourly mkt - 50th", "hourly mkt - 75th", "hourly mkt - 90th", "hourly at target", _
"mid to hourly target delta | %", "#ees", "annual base min", "annual base mid", "annual base max", "salary at target", _
"market percentile of salary mid", "annualized base min", "annualized base mid", "annualized base max", "annu. base mkt - 10th", _
"annu. base mkt - 25th", "annu. base mkt - 50th", "annu. base mkt - 75th", "annu. base mkt - 90th", "annu. base at target", "mid to annu. target delta | %"
'do nothing
Case Else
.UsedRange.Columns(I).Delete
End Select
Next I
End With
End Sub
________________________________________
Private Sub CheckBox8_Click()
'delete columns that are not referenced
Dim k, I As Long
With Sheets("byemployee")
k = .UsedRange.Columns.Count
For I = k To 1 Step -1
Select Case LCase$(.UsedRange.Cells(1, I))
Case "employee id", "position title", "job code", "employee name", "employee dept", "salary", "market percentile of base salary", _
"hourly rate", "market percentile of hourly rate", "annualized base pay", "market percentile of annu. base", "annual base min", _
"annual base mid", "annual base max", "salary compa-ratio", "salary range penetration", "hourly base min", "hourly base mid", _
"hourly base max", "hourly compa-ratio", "hourly range penetration", "annualized base min", "annualized base mid", "annualized base max", _
"annualized compa-ratio", "annualized range penetration", "target market-ratio"
'do nothing
Case Else
.UsedRange.Columns(I).Delete
End Select
Next I
End With
End Sub