1
votes

Here is what I am trying to achieve, I have an excel sheet with item numbers in A3:A5002 and various information pertaining to the item in columns B:FH. The length of the sheet changes as items are discontinued. The problem I have is the sheet contains a lot of formulas, data validation lists, and existing VBA codes that get messed up when the rows are deleted from the sheet. The Sub I wish to create would look at the item number range for blank cells, then reset the entire row back to the starting values (see code below). Then I'd like to be able to sort those blank cells to the bottom of the sheet.

The below code works to reset the entire sheet. You will be able to see in this code, the three different starting values found on the sheet as well as the cell ranges for each. Can someone help me change this code to only reset rows that that don't have a value in the Item Number Range A3:A5002?

Option Explicit
Option Compare Text
Private WS          As Worksheet
Private WSRange     As Range
Private BlankRng    As Range
Private SelRng      As Range
Private TypRng      As Range

Sub ResetSheet()

'when I tried to brake the set line up using " _" to make it easier to read, it errors out.
Set BlankRng = WS.Range("A3:A5002,T3:T5002,W3:W5002,Y3:AA5002,AC3:AF5002,AI3:AM5002,AO3:AR5002,BA3:BO5002,CF3:CH5002,CK3:CK5002,CM3:CU5002,CW3:CX5002,DF3:DF5002,DU3:DU5002")
Set SelRng = WS.Range("B3:B5002,U3:V5002,AH3:AH5002,AN3:AN5002,AS3:AU5002,CI3:CJ5002,CL3:CL5002,CV3:CV5002,CY3:DE5002")
Set TypRng = WS.Range("S3:S5002")
    BlankRng.Value = ""
    SelRng.Value = " --Select--"
    TypRng.Value = "Type"
End Sub

I was wondering, would it be possible to create a function called "ResetRow" that I could then use like this, (code below is just a guess)

Sub Idea()
Dim ITEM as range
Set ITEM = range("A3:A5002")
For each cell in ITEM
    If Not ActiveCell.Value = "" Then
        ActiveCell.EntireRow.ResetRow
Next Cell
2

2 Answers

1
votes

Without For loops:

Option Explicit

Sub ResetSheet()
    Const USED_RANGE    As String = "A2:FH5002"
    Const BLANKS_COLS   As String = "A2:A5002,T2:T5002,W2:W5002,Y2:AA5002,AC2:AF5002,AI2:AM5002,AO2:AR5002,BA2:BO5002,CF2:CH5002,CK2:CK5002,CM2:CU5002,CW2:CX5002,DF2:DF5002,DU2:DU5002"
    Const SELECT_COLS   As String = "B2:B5002,U2:V5002,AH2:AH5002,AN2:AN5002,AS2:AU5002,CI2:CJ5002,CL2:CL5002,CV2:CV5002,CY2:DE5002"
    Const TYPES_COLS    As String = "S2:S5002"
    Const BLANKS_VAL    As String = vbNullString
    Const SELECTS_VAL   As String = " --Select--"
    Const TYPES_VAL     As String = "Type"

    Dim ws As Worksheet, ur As Range

    Set ws = ActiveSheet
    Set ur = ws.Range(USED_RANGE)

    Application.ScreenUpdating = False
    With ur
        .AutoFilter Field:=1, Criteria1:="="
        If .SpecialCells(xlCellTypeVisible).Count > 1 Then
            .Range(BLANKS_COLS).Value2 = BLANKS_VAL
            .Range(SELECT_COLS).Value2 = SELECTS_VAL
            .Range(TYPES_COLS).Value2 = TYPES_VAL
        End If
        .AutoFilter
    End With
    ws.Cells(5003, 1).EntireRow.Delete
    Application.ScreenUpdating = True
End Sub
0
votes

I'm in favor of your simple ResetRow function, because it's far easier to maintain. The way you have your ranges set up may be prone to errors (at least it is for me), so my implementation tries to make it a little simpler.

EDIT: passing the worksheet object to the reset routine guarantees which worksheet is targeted.

Option Explicit

Sub test()
    Dim ws As Worksheet
    Dim lastrow As Long
    Dim i As Long

    '--- disable updates to run much quicker
    Application.EnableEvents = False
    Application.ScreenUpdating = False

    Set ws = ActiveSheet
    lastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    For i = 3 To lastrow
        If ws.Cells(i, 1) = "" Then
            ResetRow ws, i
        End If
    Next i

    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Sub ResetRow(ws As Worksheet, rowNumber As Long)
    Dim blankCols() As String
    Dim selCols() As String
    Dim typCols() As String
    Dim vc As Variant
    Dim lc As Long

    '--- initialize which columns get which setting (INCOMPLETE!! set this up correctly for your sheet)
    blankCols = Split("A,T,Y,Z,AA,AC,AD,AE,AF,AI,AJ,AK,AL,AM", ",", , vbTextCompare)
    selCols = Split("B,U,V,AH,AN,AS,CI,CJ,CL,CV", ",", , vbTextCompare)
    typCols = Split("S", ",", , vbTextCompare)

    '--- reset each field to the default setting
    For Each vc In blankCols
        lc = ws.Range(vc & 1).Column
        ws.Cells(rowNumber, lc).Value = ""
    Next vc
    For Each vc In selCols
        lc = ws.Range(vc & 1).Column
        ws.Cells(rowNumber, lc).Value = "-- Select --"
    Next vc
    For Each vc In typCols
        lc = ws.Range(vc & 1).Column
        ws.Cells(rowNumber, lc).Value = "Type"
    Next vc

End Sub