Guinaba
Board Regular
- Joined
- Sep 19, 2018
- Messages
- 218
- Office Version
- 2016
- Platform
- Windows
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
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