PDA

View Full Version : [SOLVED] Clear Data Module - Clearing Header when No Data



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

Paul_Hossler
05-09-2017, 12:04 PM
Try just .Rows.Count instead of .UsedRange.Rows.Count




With wsMOST
Range(.Rows(rowDataStartMOST), .Rows(.Rows.Count)).ClearContents
End With

pawcoyote
05-09-2017, 01:34 PM
That did it, can you tell me why it was deleting Row 4 when no data was in Row 5? UsedRange.Rows.Count did that start counting at Row 4 so it thought it was where it should start deleting when no data is found in Row 5?

SamT
05-09-2017, 01:44 PM
With no data below row 4, UsedRange.Rows.Count = bottom data row = 4

Range(.Rows(5), .Rows(4)).ClearContents
is the same as
Range(.Rows(4), .Rows(5)).ClearContents