Cleaning non continuous range

[Guinaba]

Hi experts,

Before adding the data into col. K, I have to clean the previous records from the col. K. The issue is that is not a continuos range becasue when I am adding the values I filter based on other col. So anyone has a sugestions how can I clear the data from column K considering the empty cells?

'declare object variable to hold reference to cells to clear
Dim PORange As Range
Dim i As Long
Dim LastRow As Long

'Selecting the correct sheet
Worksheets("MRPReqLive").Activate

'identify cells to clear
Set PORange = ThisWorkbook.Worksheets("MRPReqLive").Range("K2", Range("K1").End(xlDown))

LastRow = ThisWorkbook.Worksheets("MRPReqLive").Cells(Rows.Count, "K").End(xlDown).Row

Application.ScreenUpdating = False

'Inputbox to insert the date factor
DaysToMRPDate = TextBox1.Value
If DaysToMRPDate = "" Then
Exit Sub
End If

'Selecting the correct sheet
Worksheets("MRPReqLive").Activate

'Deleting previous values from AddDayFactor
For i = 1 To LastRow Step -1
If Cell.Value <> " " Then
Cell.Clear
End If
Next i

'Filter all the Purchase Order <> 0
ActiveSheet.Range("a1", ActiveSheet.Range("a1").End(xlDown)).AutoFilter Field:=5, Criteria1:="<>0", _
Operator:=xlAnd

'Copy the value from the user form into the AddDayFactor
Range("K2", Range("K1").End(xlDown)).Value = TextBox1.Value

'Remove filter
ActiveSheet.Range("a1", ActiveSheet.Range("a1").End(xlDown)).AutoFilter Field:=5

'Refreshing worksheet
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

Worksheets("MRPReqPivot").Activate

MsgBox ("Data is added successfully")

Application.ScreenUpdating = True

End Sub

 

[davesexcel]

range("K2:K" & cells(rows.count."K").end(xlup).row).clear

 

[Yongle]

@davesexcel
- minor typo - should be " , " (after rows.count)
Range("K2:K" & Cells(Rows.Count , "K").End(xlUp).Row).Clear

@Guinaba
.ClearContents clears value only
.Clear also removes formatting
- choose whichever is best for you

Are you trying to clear only the filtered row items in K?
If there is NOTHING below your data, then the same code could be simplified slightly

filter the data first and then ...

Range("K2:K" & Rows.Count).ClearContents

Personally (especially when reviewing the code later) I prefer it to be obvious that only filtered cells are being cleared

Range("K2:K" & Rows.Count).SpecialCells(xlCellTypeVisible).ClearContents

 

[davesexcel]

- minor typo - should be " , " (after rows.count)

Ah yes thanks, been something wrong with my typing lately.

 

[Yongle]

Ah yes thanks, been something wrong with my typing lately

@davesexcel Is it being distorted by too much Xmas spirit ? ??

 

[Guinaba]

Thanks @Yongle for your suggestion, I have added the code below but didn't work as expected, not sure if I am missing any thing


Private Sub CommandButton1_Click()

'declare object variable to hold reference to cells to clear
Dim PORange As Range

Application.ScreenUpdating = False

'Selecting the correct sheet
Worksheets("MRPReqLive").Activate

'Inputbox to insert the date factor
DaysToMRPDate = TextBox1.Value
If DaysToMRPDate = "" Then
Exit Sub
End If

'Filter all the Purchase Order <> 0
ActiveSheet.Range("a1", ActiveSheet.Range("a1").End(xlDown)).AutoFilter Field:=5, Criteria1:="<>0", _
Operator:=xlAnd

'Clear range before adding the new records
Set PORange = ThisWorkbook.Worksheets("MRPReqLive").Range("K2", Range("K1").End(xlDown))
PORange.ClearContents


'Copy the value from the user form into the AddDayFactor
Range("K2", Range("K1").End(xlDown)).Value = TextBox1.Value

'Remove filter
ActiveSheet.Range("a1", ActiveSheet.Range("a1").End(xlDown)).AutoFilter Field:=5

 

[Guinaba]

@Yongle forgot to mention...have tried both options:

'Clear range before adding the new records
Set PORange = ThisWorkbook.Worksheets("MRPReqLive").Range("K2", Range("K1").End(xlDown))
PORange.ClearContents

and

'Clear range before adding the new records
Set PORange = ThisWorkbook.Worksheets("MRPReqLive").Range("K2", Range("K1").End(xlDown))
PORange.SpecialCells(xlCellTypeVisible).ClearContents


Cheers,

Gilly

 

[Yongle]

@Yongle forgot to mention...have tried both options:

You asked for help but did not try ANY of the solutions we provided
- your issue is caused by using End(xlDown) which is an unreliable way find the last used cell in a column

I leave it to you to work out why ...
Range("K1").End(xlDown)
might give a different result to ...
Range("K" & Rows.Count).End(xlUp)

This may help ...
put cursor in K1 and then {End}{Down Arrow}
put cursor in K10484576 (or K65536 if using Excel 2003) and then {End}{Up Arrow}

This should work for you assuming that there is nothing below the data - last used cell is not used, all visible cells in the column below row 1 are cleared

ThisWorkbook.Worksheets("MRPReqLive").Range("K2:K" & Rows.Count).SpecialCells(xlCellTypeVisible).ClearContents

 

[Guinaba]

@Yongle I did trid both otpions suggested...anyway the last code proposed by you worked perfectly...thanks!

 

[Yongle]

Excellent