pawcoyote
05-09-2017, 10:38 AM
Good afternoon all,
I am working on a sheet that has a macro to delete data from it when I click on the macro button. It works great when I have data in the data cells but when I don't it deletes the header information. I have the same macro that works properly on another sheet.
I have the headers defined in a Global Module and and the ClearContents in the Clear Module. I am not sure why it clears the header in Row 4 if there is no data in Row 5.
' Global ModuleOption Explicit
' Updated 5/9/17
' You need to update this mod everytime you add a new sheet or if you modify where the Headers and Data start and end.
'
Public Const rowHeaderMDS As Long = 5 'MDS Sheet Header Start Row
Public Const rowDataStartMDS As Long = 7 'MDS Sheet Data Start Row
Public Const rowHeaderMOST As Long = 4 'MOST Sheet Header Start Row (Fixed Starting row 3/23/17)
Public Const rowDataStartMOST As Long = 5 'MOST Sheet Data Start Row (Fixed Starting row 3/23/17)
Public Const rowHeaderPreMDS As Long = 5 'PreMDS Sheet Header Start Row
Public Const rowDataStartPreMDS As Long = 7 'PreMDS Sheet Data Start Row
Public Const rowHeaderNames As Long = 1 'Names Sheet Header Start Row
Public Const rowDataStartNames As Long = 2 'Names Sheet Data Start Row
Public headerMDS As Range, headerMOST As Range, headerPreMDS As Range, headerNames As Range 'Updated As Range Most 5/9/17
Public rowDataEndMDS As Long, rowDataEndMOST As Long, rowDataEndPreMDS As Long, rowDataEndNames As Long 'Updated As Long Most 5/9/17
Public rNames As Range
Public rStatesRegions As Range
Public wsMDS As Worksheet, wsMOST As Worksheet, wsPreMDS As Worksheet, wsNames As Worksheet
Public aRequiredIfClient As Variant
Public aNoDupsAllowed As Variant
Public rMDS As Range, rMOST As Range, rPreMDS As Range
' Clear Module
Sub Clear_MOST()
Set wsMOST = Worksheets("MOST Equipment Add")
If MsgBox("Do NOT RUN if there is no Data on Sheet!! Are you SURE you want to clear all the data on " & wsMOST.Name & "???", vbQuestion + vbYesNo + vbDefaultButton2, "Clear " & wsMOST.Name) = vbNo Then Exit Sub
UnProtectMOST
With wsMOST
Range(.Rows(rowDataStartMOST), .Rows(.UsedRange.Rows.Count)).ClearContents 'Added "ClearContents" instead of "Clear"
End With
ProtectMOST
End Sub
I am working on a sheet that has a macro to delete data from it when I click on the macro button. It works great when I have data in the data cells but when I don't it deletes the header information. I have the same macro that works properly on another sheet.
I have the headers defined in a Global Module and and the ClearContents in the Clear Module. I am not sure why it clears the header in Row 4 if there is no data in Row 5.
' Global ModuleOption Explicit
' Updated 5/9/17
' You need to update this mod everytime you add a new sheet or if you modify where the Headers and Data start and end.
'
Public Const rowHeaderMDS As Long = 5 'MDS Sheet Header Start Row
Public Const rowDataStartMDS As Long = 7 'MDS Sheet Data Start Row
Public Const rowHeaderMOST As Long = 4 'MOST Sheet Header Start Row (Fixed Starting row 3/23/17)
Public Const rowDataStartMOST As Long = 5 'MOST Sheet Data Start Row (Fixed Starting row 3/23/17)
Public Const rowHeaderPreMDS As Long = 5 'PreMDS Sheet Header Start Row
Public Const rowDataStartPreMDS As Long = 7 'PreMDS Sheet Data Start Row
Public Const rowHeaderNames As Long = 1 'Names Sheet Header Start Row
Public Const rowDataStartNames As Long = 2 'Names Sheet Data Start Row
Public headerMDS As Range, headerMOST As Range, headerPreMDS As Range, headerNames As Range 'Updated As Range Most 5/9/17
Public rowDataEndMDS As Long, rowDataEndMOST As Long, rowDataEndPreMDS As Long, rowDataEndNames As Long 'Updated As Long Most 5/9/17
Public rNames As Range
Public rStatesRegions As Range
Public wsMDS As Worksheet, wsMOST As Worksheet, wsPreMDS As Worksheet, wsNames As Worksheet
Public aRequiredIfClient As Variant
Public aNoDupsAllowed As Variant
Public rMDS As Range, rMOST As Range, rPreMDS As Range
' Clear Module
Sub Clear_MOST()
Set wsMOST = Worksheets("MOST Equipment Add")
If MsgBox("Do NOT RUN if there is no Data on Sheet!! Are you SURE you want to clear all the data on " & wsMOST.Name & "???", vbQuestion + vbYesNo + vbDefaultButton2, "Clear " & wsMOST.Name) = vbNo Then Exit Sub
UnProtectMOST
With wsMOST
Range(.Rows(rowDataStartMOST), .Rows(.UsedRange.Rows.Count)).ClearContents 'Added "ClearContents" instead of "Clear"
End With
ProtectMOST
End Sub