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