Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repeat fill on header
I have a report that has an account header in ColB followed by several rows
of data in ColF. The header is an account number and description such as "4100 Sales". At the end of the data I have a Total for that account. The following code works to extract the number from the header and repeat it for each line of data related to that header in Col A. The code works but is very slow. Can anyone suggest an improvement to speed it up over 3500 to 4500 rows. Sub FillHelper() Dim lLastRow As Long, lRow As Long Dim sAccName As String Sheets("GL").Select With ActiveSheet Range("F3").Select lLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row lRow = 2 Do While lRow <= lLastRow If Trim(.Cells(lRow, "B")) < "" Then sAccName = .Cells(lRow, "B") If Len(Trim(.Cells(lRow, "B"))) = 0 Then .Cells(lRow, "A") = Left(sAccName, 4) lRow = lRow + 1 Loop End With End Sub I must acknowledge that this is an adaptation of a solution provided by Nigel to a previous question in July. Cheers -- Jim |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repeat fill on header
I would try to disple screenupdating and other events in the workbook while
the code is running Sub FillHelper() Application.EnableEvents = False Application.ScreenUpdating = False 'enter you code here Application.EnableEvents = True Application.ScreenUpdating = True exit sub "Jim G" wrote: I have a report that has an account header in ColB followed by several rows of data in ColF. The header is an account number and description such as "4100 Sales". At the end of the data I have a Total for that account. The following code works to extract the number from the header and repeat it for each line of data related to that header in Col A. The code works but is very slow. Can anyone suggest an improvement to speed it up over 3500 to 4500 rows. Sub FillHelper() Dim lLastRow As Long, lRow As Long Dim sAccName As String Sheets("GL").Select With ActiveSheet Range("F3").Select lLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row lRow = 2 Do While lRow <= lLastRow If Trim(.Cells(lRow, "B")) < "" Then sAccName = .Cells(lRow, "B") If Len(Trim(.Cells(lRow, "B"))) = 0 Then .Cells(lRow, "A") = Left(sAccName, 4) lRow = lRow + 1 Loop End With End Sub I must acknowledge that this is an adaptation of a solution provided by Nigel to a previous question in July. Cheers -- Jim |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repeat fill on header
I think you told me once that all macros should include this...Thanks for the
reminder. Worked perfectly. Thanks -- Jim "Joel" wrote: I would try to disple screenupdating and other events in the workbook while the code is running Sub FillHelper() Application.EnableEvents = False Application.ScreenUpdating = False 'enter you code here Application.EnableEvents = True Application.ScreenUpdating = True exit sub "Jim G" wrote: I have a report that has an account header in ColB followed by several rows of data in ColF. The header is an account number and description such as "4100 Sales". At the end of the data I have a Total for that account. The following code works to extract the number from the header and repeat it for each line of data related to that header in Col A. The code works but is very slow. Can anyone suggest an improvement to speed it up over 3500 to 4500 rows. Sub FillHelper() Dim lLastRow As Long, lRow As Long Dim sAccName As String Sheets("GL").Select With ActiveSheet Range("F3").Select lLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row lRow = 2 Do While lRow <= lLastRow If Trim(.Cells(lRow, "B")) < "" Then sAccName = .Cells(lRow, "B") If Len(Trim(.Cells(lRow, "B"))) = 0 Then .Cells(lRow, "A") = Left(sAccName, 4) lRow = lRow + 1 Loop End With End Sub I must acknowledge that this is an adaptation of a solution provided by Nigel to a previous question in July. Cheers -- Jim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Repeat Rows Across not in Header | Excel Discussion (Misc queries) | |||
How to set Header Row to repeat on each page | Excel Discussion (Misc queries) | |||
Header repeat across Sheets? | Excel Discussion (Misc queries) | |||
How to repeat a row or two as a header on the next page. | New Users to Excel | |||
Is it possible to repeat a header row at the top of each Excel pag | New Users to Excel |