VBA Express : Excel - Code Rows Only as Required

Excel

Code Rows Only as Required

Ease of Use

Intermediate

Version tested with

2000

Submitted by:

johnske

Description:

On reaching the last required entry in a row, all formats and conditional formulas are copied to the next row and column A in this new row is then selected.

Discussion:

I find that having unused formulas in very many 'pre-prepared' rows is extremely wasteful of memory resources when it's considered that a single line (or two) of VBA code can easily replace a thousand (or more) lines of spread sheet code. The best way to utilize resources is to use VBA code to do everything as required using one or more of the worksheet events such as Selection_Change to activate the code. The next best way is to only code one line and use VBA to copy the code to a new line only when it's required. This is a simple example of such a usage, the cells in the zipped example are conditionally formatted to show which sales person is selling the most (gold), the second-most (silver), and third-most (bronze) of a given item in each column i.e. it's a simple performance-oriented database. Although the code in this example specifically copies only the formats and conditions, it should be obvious that it can be easily modified to copy formulas as well.

Code:

instructions for use

			Option Explicit 
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
     
     '**************************
     'put your own last column below
    Const LastCol As String = "$K" 
     '**************************
     
     '//hide gridlines and zero values
    With ActiveWindow 
        .DisplayGridlines = False 
        .DisplayZeros = False 
    End With 
     
     '//if reached the last entry in this row
    If Left(Target.Address, 2) = LastCol Then 
         '//copy the row
        Rows(Target.Row).Copy 
         '//paste the formats in the next row
        Rows(Target.Row).Offset(1, 0).PasteSpecial xlPasteFormats 
         '//select column A in the new row
        Intersect(Target.EntireRow.Offset(1, 0), Columns(1)).Select 
    End If 
     
End Sub

How to use:

Open an Excel workbook
Select Tools/Macro/Visual Basic Editor
In the VBE window, select Tools/Project Explorer
Select the Sheet module you want this applied to
Copy and paste the code into the sheet Module
Now select File/Close and Return To Microsoft Excel
Dont forget to save your changes...

Test the code:

Before you paste the code
Draw up some headings etc., format a row as you want it, then paste the code
Start entering data
Alternatively, download the attachment, extract the enclosed workbook
Enter data in the next empty row and observe what happens when you reach the last entry

Sample File:

CodeAsYouGo.zip 8.98KB

Approved by mdmackillop

This entry has been viewed 191 times.