Dear Experts,
I have attached one VBA Code which we are using regularly
for huge data it is running very slow
is there any way to make it faster
Pls Suggest
Thanks in advance
Girish
--
Warm Regards,
*Girish Sherigar*
* (¨`·.·´¨) *
*`·.¸(¨`·.·´¨)* *Keep*
* (¨`·.·´¨)¸.·´ Smiling!*
* `·.¸.·´*
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
Dim lcnt_t25 As Integer
Dim lcnt_t9 As Integer
Dim lcnt_t11 As Integer
Dim lcnt_t17 As Integer
Dim lcnt_t22 As Integer
Dim lcnt_t23 As Integer
Dim lcnt_t24 As Integer
Dim lcnt_t26 As Integer
Dim lcnt_t29 As Integer
Dim lcnt_t30 As Integer
Dim lcnt_t31 As Integer
Dim lcnt_t35 As Integer
Dim lcnp As String
Dim linp_nop_id As String
Dim linp_start_date As String
Dim ldest_name As String
Dim lend_date As String
Dim ldest_id As String
Dim ldest_parent_id As String
Dim lstart_time As String
Dim lend_time As String
Dim lrate As String
Dim lrow_count As Integer
Sub Auto_Open()
ActiveWorkbook.Sheets("Overview").Activate
Worksheets("Overview").Cells(10, 4).ClearContents
Worksheets("Overview").Cells(12, 4).ClearContents
Worksheets("Overview").Cells(20, 4) = "Ready"
End Sub
Sub CheckCNPsOverall()
Dim CurrCell
'Dim ltmp_string As String
'Dim lcnt As Integer
Dim lrow_count As Integer
Worksheets("Overview").Cells(20, 4) = "Checking Data"
Application.ScreenUpdating = False
ActiveWorkbook.Worksheets("NEW").Activate
For Each CurrCell In Intersect(ActiveSheet.UsedRange,
ActiveSheet.Range("B2:B65536"))
If CurrCell.Value = "" Then
Exit For
End If
CurrCell.Offset(0, 1) = ClearString(CurrCell.Value)
CurrCell.Offset(0, 3).FormulaR1C1 = "=CONCATENATE(RC1,RC3)"
Next
ActiveWorkbook.Worksheets("OLD").Select
For Each CurrCell In Intersect(ActiveSheet.UsedRange,
ActiveSheet.Range("B2:B65536"))
If CurrCell.Value = "" Then
Exit For
End If
CurrCell.Offset(0, 1) = ClearString(CurrCell.Value)
CurrCell.Offset(0, 3).FormulaR1C1 = "=CONCATENATE(RC1,RC3)"
Next
ActiveWorkbook.Worksheets("MASTER").Select
For Each CurrCell In Intersect(ActiveSheet.UsedRange,
ActiveSheet.Range("A2:A65536"))
If CurrCell.Value = "" Then
Exit For
End If
CurrCell.Offset(0, 1) = ClearString(CurrCell.Value)
Next
ActiveWorkbook.Worksheets("NEW").Select
For Each CurrCell In Intersect(ActiveSheet.UsedRange,
ActiveSheet.Range("C2:C65536"))
If CurrCell.Value = "" Then
Exit For
End If
'Original formula
=IF(ISNA(VLOOKUP(E2,OLD!E:E,1,FALSE)),IF(ISNA(VLOOKUP(A2,OLD!A:A,1,FALSE)),IF(ISNA(VLOOKUP(C2,OLD!C:C,1,FALSE)),"ADD:NEW",CONCATENATE("ADD:",VLOOKUP(C2,OLD!C:D,2,FALSE))),IF(ISNA(VLOOKUP(C2,OLD!C:C,1,FALSE)),"CHG:NEW",CONCATENATE("CHG:",VLOOKUP(C2,OLD!C:D,2,FALSE)))),"OK")
'Formula 2
=IF(ISNA(VLOOKUP(E2,OLD!E:E,1,FALSE)),IF(ISNA(VLOOKUP(A2,OLD!A:A,1,FALSE)),IF(ISNA(VLOOKUP(C2,OLD!C:C,1,FALSE)),IF(ISNA(VLOOKUP(C2,MASTER!B:B,1,FALSE)),"ADD:NEW",CONCATENATE("ADD:",VLOOKUP(C2,MASTER!B:C,2,FALSE))),CONCATENATE("ADD:",VLOOKUP(C2,OLD!C:D,2,FALSE))),IF(ISNA(VLOOKUP(C2,OLD!C:C,1,FALSE)),"CHG:NEW",CONCATENATE("CHG:",VLOOKUP(C2,OLD!C:D,2,FALSE)))),CONCATENATE("OK:",VLOOKUP(C2,OLD!C:D,2,FALSE)))
CurrCell.Offset(0, 3).FormulaR1C1 =
"=IF(ISNA(VLOOKUP(RC5,OLD!C5,1,FALSE)),IF(ISNA(VLOOKUP(RC1,OLD!C1,1,FALSE)),IF(ISNA(VLOOKUP(RC3,OLD!C3,1,FALSE)),IF(ISNA(VLOOKUP(RC3,MASTER!C2,1,FALSE)),""ADD:NEW"",CONCATENATE(""ADD:"",VLOOKUP(RC3,MASTER!C2:C3,2,FALSE))),CONCATENATE(""ADD:"",VLOOKUP(RC3,OLD!C3:C4,2,FALSE))),IF(ISNA(VLOOKUP(RC3,OLD!C3,1,FALSE)),""CHG:NEW"",CONCATENATE(""CHG:"",VLOOKUP(RC3,OLD!C3:C4,2,FALSE)))),CONCATENATE(""OK:"",VLOOKUP(RC3,OLD!C3:C4,2,FALSE)))"
'CurrCell.Offset(0, 3) = Sheets("TEMP").Range("B1").Value
CurrCell.Offset(0, 3) = CurrCell.Offset(0, 3).Value
Next
ActiveWorkbook.Worksheets("OLD").Select
For Each CurrCell In Intersect(ActiveSheet.UsedRange,
ActiveSheet.Range("C2:C65536"))
If CurrCell.Value = "" Then
Exit For
End If
'Original formula
=IF(ISNA(VLOOKUP(E2,NEW!E:E,1,FALSE)),IF(ISNA(VLOOKUP(A2,NEW!A:A,1,FALSE)),"DEL",VLOOKUP(A2,NEW!A:F,6,FALSE)),"OK")
CurrCell.Offset(0, 3).FormulaR1C1 =
"=IF(ISNA(VLOOKUP(RC5,NEW!C5,1,FALSE)),IF(ISNA(VLOOKUP(RC1,NEW!C1,1,FALSE)),""DEL"",VLOOKUP(RC1,NEW!C1:C6,6,FALSE)),""OK"")"
CurrCell.Offset(0, 3) = CurrCell.Offset(0, 3).Value
Next
'Call PopulateTemplates
Worksheets("Overview").Cells(20, 4) = "Complete"
MsgBox "Complete : Checking Data"
Application.ScreenUpdating = True
End Sub
Sub PopulateTemplates()
Dim CurrCell
Dim ltmp_part_dest_id As String
Dim ltmp_dest_id As String
Dim lregion_code As String
Dim ltmp_parent_id As String
Dim ltmp_string As String
Dim ltmp_number As Integer
Dim lmatch_row As Integer
Dim lcheck_status As String
Dim lcheck_if_exists As String
Dim lclean_dest_name As String
Dim lflg_new_first As Boolean
lcnt_t25 = 2
lcnt_t9 = 2
lcnt_t11 = 2
lcnt_t17 = 2
lcnt_t22 = 2
lcnt_t23 = 2
lcnt_t24 = 2
lcnt_t26 = 2
lcnt_t29 = 2
lcnt_t30 = 2
lcnt_t31 = 2
lcnt_t35 = 2
Worksheets("Overview").Cells(20, 4) = "Populating Templates"
Application.ScreenUpdating = False
linp_nop_id = Sheets("Overview").Cells(10, 4).Value
linp_start_date = Sheets("Overview").Cells(12, 4).Value
'If (linp_nop_id = "") Then
' Read ("Enter the Network Operator Id")
'End If
Sheets("T9").Range("D:D").NumberFormat = "@"
Sheets("T11").Range("D:D").NumberFormat = "@"
Sheets("T17").Range("D:D").NumberFormat = "@"
Sheets("T24").Range("D:D").NumberFormat = "@"
Sheets("T26").Range("M:N").NumberFormat = "@"
Sheets("T35").Range("AI:AI").NumberFormat = "@"
Call ClearTemplates
lend_date = "12/31/2999"
lstart_time = "00:00"
lend_time = "23:59"
Call SortMaster
lflg_new_first = True
ActiveWorkbook.Worksheets("NEW").Activate
For Each CurrCell In Intersect(ActiveSheet.UsedRange,
ActiveSheet.Range("F2:F65536"))
If CurrCell.Value = "" Then
Exit For
End If
ldest_name = CurrCell.Offset(0, -4).Value
ltmp_part_dest_id = CurrCell.Offset(0, 1) & "ZZ"
lcnp = CurrCell.Offset(0, -5).Value
ldest_parent_id = "PARENT NOT BUILT"
lrate = CurrCell.Offset(0, 3).Value
lcheck_status = CurrCell.Value
lclean_dest_name = CurrCell.Offset(0, -3).Value
If (Left(lcheck_status, 3) = "ADD" Or Left(lcheck_status, 3) = "CHG") Then
If Right(lcheck_status, 3) = "NEW" Then
'On Error Resume Next
'Err.Clear
Sheets("TEMP").Range("B1").FormulaR1C1 = "=IF(ISNA(VLOOKUP(""" &
lclean_dest_name & """, MASTER!C2:C3, 2, FALSE)),""NEW"",VLOOKUP(""" &
lclean_dest_name & """, MASTER!C2:C3, 2, FALSE))"
If (Sheets("TEMP").Range("B1").Value = "NEW") Then
Sheets("TEMP").Range("A1").FormulaR1C1 = "=VLOOKUP(""" &
ltmp_part_dest_id & """, MASTER!C3, 1, TRUE)"
lflg_new_first = True
ltmp_dest_id = Sheets("TEMP").Range("A1").Value
'If Err.Number <> 0 Then
If (Right(ltmp_dest_id, 1) = "Z") Then
ldest_id = Left(ltmp_dest_id, 6) &
Chr(Asc(Mid(ltmp_dest_id, 7, 1)) + 1) & "A"
Else
ldest_id = Left(ltmp_dest_id, 7) &
Chr(Asc(Right(ltmp_dest_id, 1)) + 1)
End If
'MsgBox ("New Dest Id : " & ldest_id & " Name : " & ldest_name)
'Get Master data
Sheets("TEMP").Range("A2").FormulaR1C1 = "=VLOOKUP(""" &
ltmp_dest_id & """, MASTER!C3:C5, 3, FALSE)"
lregion_code = Sheets("TEMP").Range("A2").Value
Call UpdateMaster(ldest_id, ldest_name, lregion_code)
Call SortMaster
'End If
'Generate parent id for T23
ltmp_parent_id = lregion_code & Left(ldest_id, 6)
If (Right(ltmp_parent_id, 1) = "X") Then
ldest_parent_id = Left(ltmp_parent_id,
(WorksheetFunction.Find("X", ltmp_parent_id, 1) - 1))
Else
ldest_parent_id = ltmp_parent_id
End If
Else
ldest_id = Sheets("TEMP").Range("B1").Value
Sheets("TEMP").Range("B1").FormulaR1C1 = "=VLOOKUP(""" &
ldest_id & """, MASTER!C3:C4, 2, FALSE)"
ldest_parent_id = Sheets("TEMP").Range("B1").Value
lflg_new_first = False
End If
Else
ldest_id = Right(lcheck_status, 8)
End If
If Left(lcheck_status, 3) = "ADD" Then
Call PopulateT9
Call PopulateT11
Call PopulateT17
End If
If (lcheck_status = "ADD:NEW" Or lcheck_status = "CHG:NEW") Then
If (lflg_new_first) Then
Call PopulateT22
Call PopulateT23
End If
End If
Call PopulateT24
If (lcheck_status = "ADD:NEW" Or lcheck_status = "CHG:NEW") Then
If (lflg_new_first) Then
Call PopulateT25
Call PopulateT26
Call PopulateT29
Call PopulateT30
Call PopulateT31
lflg_new_first = False
End If
End If
ElseIf (Left(lcheck_status, 2) = "OK") Then
ldest_id = Right(lcheck_status, Len(lcheck_status) - 3)
End If
Call PopulateT35
Next
Sheets("TEMP").Range("A1:B2").ClearContents
Application.ScreenUpdating = True
ActiveWorkbook.Worksheets("Overview").Select
Worksheets("Overview").Cells(20, 4) = "Complete"
MsgBox ("Complete : Populating Templates")
End Sub
Sub PopulateT9()
Sheets("T9").Activate
Cells(lcnt_t9, 3) = "A"
Cells(lcnt_t9, 4) = lcnp
lcnt_t9 = lcnt_t9 + 1
End Sub
Sub PopulateT11()
Sheets("T11").Activate
Cells(lcnt_t11, 3) = "A"
Cells(lcnt_t11, 4) = lcnp
Cells(lcnt_t11, 5) = "INT"
Cells(lcnt_t11, 6) = "*"
Cells(lcnt_t11, 7) = linp_start_date
Cells(lcnt_t11, 8) = lend_date
lcnt_t11 = lcnt_t11 + 1
End Sub
Sub PopulateT17()
Sheets("T17").Activate
Cells(lcnt_t17, 3) = "A"
Cells(lcnt_t17, 4) = lcnp
Cells(lcnt_t17, 5) = UCase(ldest_name)
Cells(lcnt_t17, 7) = linp_start_date
Cells(lcnt_t17, 8) = lend_date
Cells(lcnt_t17, 9) = "*"
Cells(lcnt_t17, 10) = "NN"
lcnt_t17 = lcnt_t17 + 1
End Sub
Sub PopulateT22()
Sheets("T22").Activate
Cells(lcnt_t22, 3) = "A"
Cells(lcnt_t22, 4) = ldest_id
Cells(lcnt_t22, 5) = UCase(ldest_name)
Cells(lcnt_t22, 6) = "N" '??
lcnt_t22 = lcnt_t22 + 1
End Sub
Sub PopulateT23()
Sheets("T23").Activate
Cells(lcnt_t23, 3) = "A"
Cells(lcnt_t23, 4) = linp_nop_id & "-LC"
Cells(lcnt_t23, 5) = linp_start_date
Cells(lcnt_t23, 6) = lend_date
Cells(lcnt_t23, 7) = "WORLD"
Cells(lcnt_t23, 8) = ldest_parent_id
Cells(lcnt_t23, 9) = ldest_id
lcnt_t23 = lcnt_t23 + 1
End Sub
Sub PopulateT24()
Sheets("T24").Activate
Cells(lcnt_t24, 3) = "A"
Cells(lcnt_t24, 4) = lcnp
Cells(lcnt_t24, 5) = ldest_id
Cells(lcnt_t24, 6) = linp_nop_id & "-LC"
Cells(lcnt_t24, 7) = linp_start_date
Cells(lcnt_t24, 8) = lend_date
lcnt_t24 = lcnt_t24 + 1
End Sub
Sub PopulateT25()
Sheets("T25").Activate
Cells(lcnt_t25, 3) = "A"
Cells(lcnt_t25, 4) = "[AIIND+VSILD]++[" & linp_nop_id & "+" & ldest_id & "]"
Cells(lcnt_t25, 5) = "VSILD"
Cells(lcnt_t25, 6) = linp_nop_id
Cells(lcnt_t25, 7) = "B"
Cells(lcnt_t25, 8) = "O"
Cells(lcnt_t25, 9) = "DO"
Cells(lcnt_t25, 10) = "Y"
Cells(lcnt_t25, 11) = "VSILD"
Cells(lcnt_t25, 12) = "AIIND"
Cells(lcnt_t25, 13) = linp_nop_id
Cells(lcnt_t25, 14) = ldest_id
Cells(lcnt_t25, 15) = "Y"
Cells(lcnt_t25, 16) = lend_date
lcnt_t25 = lcnt_t25 + 1
End Sub
Sub PopulateT26()
Sheets("T26").Activate
Cells(lcnt_t26, 3) = "A"
Cells(lcnt_t26, 4) = "VSILD"
Cells(lcnt_t26, 5) = "O"
Cells(lcnt_t26, 6) = ldest_id
Cells(lcnt_t26, 7) = "AIIND"
Cells(lcnt_t26, 8) = ""
Cells(lcnt_t26, 9) = linp_nop_id
Cells(lcnt_t26, 13) = lstart_time
Cells(lcnt_t26, 14) = lend_time
Cells(lcnt_t26, 19) = linp_start_date
Cells(lcnt_t26, 20) = lend_date
Cells(lcnt_t26, 21) = "[AIIND+VSILD]++[" & linp_nop_id & "+" & ldest_id &
"]"
Cells(lcnt_t26, 22) = "R"
Cells(lcnt_t26, 23) = "DO"
Cells(lcnt_t26, 24) = linp_nop_id
Cells(lcnt_t26, 25) = linp_nop_id
Cells(lcnt_t26, 26) = "B"
Cells(lcnt_t26, 27) = "B"
lcnt_t26 = lcnt_t26 + 1
End Sub
Sub PopulateT29()
Sheets("T29").Activate
Cells(lcnt_t29, 3) = "A"
Cells(lcnt_t29, 4) = "VSILD"
Cells(lcnt_t29, 5) = "O"
Cells(lcnt_t29, 6) = "LCR"
Cells(lcnt_t29, 7) = "[AIIND+VSILD]++[" & linp_nop_id & "+" & ldest_id & "]"
Cells(lcnt_t29, 8) = linp_nop_id
Cells(lcnt_t29, 9) = "DO"
Cells(lcnt_t29, 10) = "B"
Cells(lcnt_t29, 11) = "LA-FA-LCR-OG"
Cells(lcnt_t29, 12) = "C"
Cells(lcnt_t29, 13) = "USD"
Cells(lcnt_t29, 14) = "TEL"
lcnt_t29 = lcnt_t29 + 1
End Sub
Sub PopulateT30()
Sheets("T30").Activate
Cells(lcnt_t30, 3) = "A"
Cells(lcnt_t30, 4) = "[AIIND+VSILD]++[" & linp_nop_id & "+" & ldest_id & "]"
Cells(lcnt_t30, 5) = "DO"
Cells(lcnt_t30, 6) = "VSILD"
Cells(lcnt_t30, 7) = linp_nop_id
Cells(lcnt_t30, 8) = "LCR"
Cells(lcnt_t30, 9) = "O"
Cells(lcnt_t30, 10) = "B"
Cells(lcnt_t30, 11) = linp_nop_id
Cells(lcnt_t30, 12) = "TRMF"
Cells(lcnt_t30, 13) = "V"
Cells(lcnt_t30, 14) = linp_nop_id
Cells(lcnt_t30, 15) = "V"
Cells(lcnt_t30, 16) = linp_start_date
Cells(lcnt_t30, 17) = lend_date
Cells(lcnt_t30, 18) = "S"
Cells(lcnt_t30, 19) = "E"
Cells(lcnt_t30, 20) = "R"
Cells(lcnt_t30, 21) = "SROU"
Cells(lcnt_t30, 22) = "N"
Cells(lcnt_t30, 23) = "N"
Cells(lcnt_t30, 24) = 0
Cells(lcnt_t30, 25) = 0
Cells(lcnt_t30, 27) = linp_nop_id & "-LC"
Cells(lcnt_t30, 29) = "I"
Cells(lcnt_t30, 30) = 0
Cells(lcnt_t30, 31) = "Y"
Cells(lcnt_t30, 32) = "N"
Cells(lcnt_t30, 33) = linp_start_date
Cells(lcnt_t30, 34) = "O"
Cells(lcnt_t30, 35) = linp_nop_id
Cells(lcnt_t30, 36) = "TRMF"
Cells(lcnt_t30, 37) = "V"
Cells(lcnt_t30, 38) = linp_nop_id
Cells(lcnt_t30, 39) = "V"
lcnt_t30 = lcnt_t30 + 1
End Sub
Sub PopulateT31()
Sheets("T31").Activate
Cells(lcnt_t31, 3) = "A"
Cells(lcnt_t31, 4) = "[AIIND+VSILD]++[" & linp_nop_id & "+" & ldest_id & "]"
Cells(lcnt_t31, 5) = "VSILD"
Cells(lcnt_t31, 6) = "*"
Cells(lcnt_t31, 7) = "*"
Cells(lcnt_t31, 8) = "*"
Cells(lcnt_t31, 9) = lstart_time
Cells(lcnt_t31, 10) = lend_time
Cells(lcnt_t31, 11) = "*"
Cells(lcnt_t31, 12) = "*"
Cells(lcnt_t31, 13) = "*"
Cells(lcnt_t31, 14) = "*"
Cells(lcnt_t31, 15) = linp_start_date
Cells(lcnt_t31, 16) = lend_date
Cells(lcnt_t31, 17) = "LCR"
Cells(lcnt_t31, 18) = linp_nop_id
Cells(lcnt_t31, 19) = "B"
Cells(lcnt_t31, 20) = "DO"
lcnt_t31 = lcnt_t31 + 1
End Sub
Sub PopulateT35()
Sheets("T35").Activate
Cells(lcnt_t35, 3) = "A"
Cells(lcnt_t35, 4) = "VSILD"
Cells(lcnt_t35, 5) = linp_nop_id
Cells(lcnt_t35, 6) = "C"
Cells(lcnt_t35, 7) = "[AIIND+VSILD]++[" & linp_nop_id & "+" & ldest_id & "]"
Cells(lcnt_t35, 8) = linp_nop_id
Cells(lcnt_t35, 9) = "VSILD"
Cells(lcnt_t35, 10) = "B"
Cells(lcnt_t35, 11) = "DO"
Cells(lcnt_t35, 12) = "TRMF"
Cells(lcnt_t35, 13) = "INTL" 'ISDN to be populated manually
Cells(lcnt_t35, 14) = "O"
Cells(lcnt_t35, 15) = linp_nop_id & "-LC"
Cells(lcnt_t35, 16) = "ALL"
Cells(lcnt_t35, 17) = linp_start_date
Cells(lcnt_t35, 20) = "USD"
Cells(lcnt_t35, 21) = "N"
Cells(lcnt_t35, 22) = "MINUTE"
Cells(lcnt_t35, 25) = lend_date
Cells(lcnt_t35, 35) = lrate
lcnt_t35 = lcnt_t35 + 1
End Sub
Sub ClearTemplates()
Sheets("T9").Activate
lrow_count = ActiveSheet.UsedRange.Rows.Count
lcol_count = ActiveSheet.UsedRange.Columns.Count
If (lrow_count > 1) Then
ActiveSheet.UsedRange.Range(Cells(2, 1), Cells(lrow_count,
lcol_count)).ClearContents
End If
Sheets("T11").Activate
lrow_count = ActiveSheet.UsedRange.Rows.Count
lcol_count = ActiveSheet.UsedRange.Columns.Count
If (lrow_count > 1) Then
ActiveSheet.UsedRange.Range(Cells(2, 1), Cells(lrow_count,
lcol_count)).ClearContents
End If
Sheets("T17").Activate
lrow_count = ActiveSheet.UsedRange.Rows.Count
lcol_count = ActiveSheet.UsedRange.Columns.Count
If (lrow_count > 1) Then
ActiveSheet.UsedRange.Range(Cells(2, 1), Cells(lrow_count,
lcol_count)).ClearContents
End If
Sheets("T22").Activate
lrow_count = ActiveSheet.UsedRange.Rows.Count
lcol_count = ActiveSheet.UsedRange.Columns.Count
If (lrow_count > 1) Then
ActiveSheet.UsedRange.Range(Cells(2, 1), Cells(lrow_count,
lcol_count)).ClearContents
End If
Sheets("T23").Activate
lrow_count = ActiveSheet.UsedRange.Rows.Count
lcol_count = ActiveSheet.UsedRange.Columns.Count
If (lrow_count > 1) Then
ActiveSheet.UsedRange.Range(Cells(2, 1), Cells(lrow_count,
lcol_count)).ClearContents
End If
Sheets("T24").Activate
lrow_count = ActiveSheet.UsedRange.Rows.Count
lcol_count = ActiveSheet.UsedRange.Columns.Count
If (lrow_count > 1) Then
ActiveSheet.UsedRange.Range(Cells(2, 1), Cells(lrow_count,
lcol_count)).ClearContents
End If
Sheets("T25").Activate
lrow_count = ActiveSheet.UsedRange.Rows.Count
lcol_count = ActiveSheet.UsedRange.Columns.Count
If (lrow_count > 1) Then
ActiveSheet.UsedRange.Range(Cells(2, 1), Cells(lrow_count,
lcol_count)).ClearContents
End If
Sheets("T26").Activate
lrow_count = ActiveSheet.UsedRange.Rows.Count
lcol_count = ActiveSheet.UsedRange.Columns.Count
If (lrow_count > 1) Then
ActiveSheet.UsedRange.Range(Cells(2, 1), Cells(lrow_count,
lcol_count)).ClearContents
End If
Sheets("T29").Activate
lrow_count = ActiveSheet.UsedRange.Rows.Count
lcol_count = ActiveSheet.UsedRange.Columns.Count
If (lrow_count > 1) Then
ActiveSheet.UsedRange.Range(Cells(2, 1), Cells(lrow_count,
lcol_count)).ClearContents
End If
Sheets("T30").Activate
lrow_count = ActiveSheet.UsedRange.Rows.Count
lcol_count = ActiveSheet.UsedRange.Columns.Count
If (lrow_count > 1) Then
ActiveSheet.UsedRange.Range(Cells(2, 1), Cells(lrow_count,
lcol_count)).ClearContents
End If
Sheets("T31").Activate
lrow_count = ActiveSheet.UsedRange.Rows.Count
lcol_count = ActiveSheet.UsedRange.Columns.Count
If (lrow_count > 1) Then
ActiveSheet.UsedRange.Range(Cells(2, 1), Cells(lrow_count,
lcol_count)).ClearContents
End If
Sheets("T35").Activate
lrow_count = ActiveSheet.UsedRange.Rows.Count
lcol_count = ActiveSheet.UsedRange.Columns.Count
If (lrow_count > 1) Then
ActiveSheet.UsedRange.Range(Cells(2, 1), Cells(lrow_count,
lcol_count)).ClearContents
End If
Sheets("TEMP").Activate
lrow_count = ActiveSheet.UsedRange.Rows.Count
lcol_count = ActiveSheet.UsedRange.Columns.Count
If (lrow_count > 1) Then
ActiveSheet.UsedRange.Range(Cells(2, 1), Cells(lrow_count,
lcol_count)).ClearContents
End If
End Sub
Sub SortMaster()
Sheets("MASTER").Activate
ActiveSheet.UsedRange.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
Sub UpdateMaster(pdest_id As String, pdest_name As String, pregion_code As
String)
Dim lrow_count As Integer
Dim ltmp_parent_id As String
Sheets("MASTER").Activate
lrow_count = ActiveSheet.UsedRange.Rows.Count
lrow_count = lrow_count + 1
Range("A" & lrow_count).Select
ActiveCell.Value = pdest_name
ActiveCell.Offset(0, 1) = ClearString(pdest_name)
ActiveCell.Offset(0, 2) = pdest_id
ltmp_parent_id = pregion_code & Left(pdest_id, 6)
If (Right(ltmp_parent_id, 1) = "X") Then
ActiveCell.Offset(0, 3) = Left(ltmp_parent_id,
(WorksheetFunction.Find("X", ltmp_parent_id, 1) - 1))
Else
ActiveCell.Offset(0, 3) = ltmp_parent_id
End If
ActiveCell.Offset(0, 4) = pregion_code
ActiveCell.Offset(0, 5) = UCase(pdest_name)
End Sub
Function ClearString(pdest_name As String) As String
Dim lcnt As Integer
Dim ltmp_string As String
ltmp_string = ""
For lcnt = 1 To Len(pdest_name)
Select Case Mid(pdest_name, lcnt, 1)
Case "A" To "Z", "a" To "z", "0" To "9"
ltmp_string = ltmp_string & Mid(pdest_name, lcnt, 1)
End Select
Next lcnt
ClearString = ltmp_string
End Function
