Luis Diaz
asked on
VBA : retain and exclude values based on a config sheet and multiple parameters
Hello experts,
I use the following procedure in order to retain values based on a config sheet and multiple parameters:
I would like to take it as a reference in order to cover the following requirement:
Retain or exclude values from a Source Sheet to a Destination Sheet based on the following parameters:
-SourceSheet (Column A): Sheet which contains the various data to report in DestinationSheet
-DestinationSheet (Column B): Sheet in which will be reported all the information based on the retention and exclude filter.
-Column Filter (Column C): Reference Column (report in alphabetical if possible) in which the filter would be applied:
-Filter String Flag (Column D): If equal 1, the filter should be applied through a contain.
Else if equal 2, the filter should be applied through an exact. In the filter string I can report multiple values. The delimiter is a semicolon.
-Removal retention Flag (Column E): If equal 1 this means that all the lines related to values reported in Column Filter which match with String Value (Column F) of Source Sheet should be report in DestinationSheet.
Else if equal to 2 or another value this means that all the lines related to values reported in Column Filter which doesn’t match with String Value (Column F) of Source Sheet should be report in DestinationSheet.
-String Value (Column F): reference value to take into account for the various flags.
I attached a dummy file with examples.
If you have questions, please don’t hesitate to contact me.
Thank you very much for your help.
Retain_exclude_values_and_based_on_.xlsm
I use the following procedure in order to retain values based on a config sheet and multiple parameters:
Sub RetentionSpecificRange2(strFlag As Integer, strColumn As String, strRetentionString, strFilterStart As String)
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With ActiveSheet
'We select the sheet so we can change the window view
.Select
'If you are in Page Break Preview Or Page Layout view go
'back to normal view, we do this for speed
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
'Turn off Page Breaks, we do this for speed
.DisplayPageBreaks = False
'Set the first and last row to loop through
'Firstrow = .UsedRange.Cells(2).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
'We loop from Lastrow to Firstrow (bottom to top)
For Lrow = Lastrow To strFilterStart Step -1
With .Cells(Lrow, strColumn)
If Not IsError(.Value) Then
bNotThereEquals = True
bNotThereContains = True
For Each el In Split(strRetentionString, ";")
If .Value = el Then
bNotThereEquals = bNotThereEquals And False
End If
If .Value Like ("*" & el & "*") Then
bNotThereContains = bNotThereContains And False
End If
Next el
If strFlag = 1 Then
If bNotThereContains = True Then .EntireRow.Delete
Else
If bNotThereEquals = True Then .EntireRow.Delete
End If
End If
End With
Next Lrow
End With
ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Su
bI would like to take it as a reference in order to cover the following requirement:
Retain or exclude values from a Source Sheet to a Destination Sheet based on the following parameters:
-SourceSheet (Column A): Sheet which contains the various data to report in DestinationSheet
-DestinationSheet (Column B): Sheet in which will be reported all the information based on the retention and exclude filter.
-Column Filter (Column C): Reference Column (report in alphabetical if possible) in which the filter would be applied:
-Filter String Flag (Column D): If equal 1, the filter should be applied through a contain.
Else if equal 2, the filter should be applied through an exact. In the filter string I can report multiple values. The delimiter is a semicolon.
-Removal retention Flag (Column E): If equal 1 this means that all the lines related to values reported in Column Filter which match with String Value (Column F) of Source Sheet should be report in DestinationSheet.
Else if equal to 2 or another value this means that all the lines related to values reported in Column Filter which doesn’t match with String Value (Column F) of Source Sheet should be report in DestinationSheet.
-String Value (Column F): reference value to take into account for the various flags.
I attached a dummy file with examples.
If you have questions, please don’t hesitate to contact me.
Thank you very much for your help.
Retain_exclude_values_and_based_on_.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I tried to add DestSheet if it doesn't exist however I was not able to properly the if condition do you know why it doesn't work the first part of the code:
Thank you very much for your help.
Sub macro()
wbname = ActiveWorkbook.Name
Set wb = Workbooks(wbname)
wb.Sheets("Config").Activate
For Each c In Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
'===========================================================
'1)-->Add the DestinationWorksheet if doesn't exist
'===========================================================
On Error Resume Next
Set DestSh = wb.Sheets(c.Offset(0, 1).Value)
On Error GoTo 0
If IsEmpty(DestSh) Or DestSh Is Nothing Then
Set DestSh = wb.Sheets.Add(After:=Sheets(wb.Sheets.Count))
DestSh.Name = c.Offset(0, 1).Value
Else
DestSh.UsedRange.Clear
End If
Set OrigSh = Sheets(c.Value)
OrigSh.Cells.Copy DestSh.Range("A1")
DestSh.Activate
RetentionSpecificRange2 c.Offset(, 3), c.Offset(, 2), c.Offset(, 5), "2", c.Offset(, 4)
Next
End Sub
Thank you very much for your help.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It works!!! Thank you very much for your help.
ASKER
Thank you again for your help.