VBA raw eraser given specified column cell Value(s)

[Cornstarch]

First of all, thank you sparing a moment reading and trying to help.

Its been couple of months that I am trying to develop a solution by myself combining idea's from other forums without success.

Target:
- From a Global file with area's and blank rows. Example in Caption 1
- Be able to let a user select from a text box the area that he wants to keep (which will also always keep blank rows).
- Result after selecting "Brazil" in Caption 2

Caption 1:

Caption 2:

Best,

Julien

 

[Peter_SSs]

As you have found, you cannot show images that are stored on your own computer. You could upload images to, say, a public file-share site and provide a link to that. However, that still has the disadvantage that helpers cannot copy from the image to test with. Better to post some sample data here in the thread that can be copied to test with. There is a link in my signature block below with suggestions about that.

 

[Cornstarch]

Very well Peter, thanks informing.

Correction bellow,

Caption 1 (Starting table):

Item	Area	Region	Sub Region/Comments
1	Brazil	Brazil	Brazil
2	EMME	EMME	Whole EMME
3	Argentina	Argentina	Argentina
4	Bolivia	Argentina	Bolivia
5	Chile	LAN	Chile
6	Colombia	LAN	Colombia
7	CAM	LAN	CAM
8	VEP	LAN	VEP
9	USA	NAFTA	USA
10	Mexico	NAFTA	Mexico
11	Egypt	Emerging Area	Egypt
12	SSA	Emerging Area	SSA
13	South Korea	Emerging Area	South Korea
14	ID and PH	Emerging Area	Indonesia and Phillippines
15	Other SEA excl PH	Emerging Area	Other SEA exclude Phillines
16	Brazil	Brazil	Brazil
17	EMME	EMME	Whole EMME
18	Argentina	Argentina	Argentina
19	Bolivia	Argentina	Bolivia
20	Chile	LAN	Chile
21	Colombia	LAN	Colombia
22	CAM	LAN	CAM
23	VEP	LAN	VEP
24	USA	NAFTA	USA
25	Mexico	NAFTA	Mexico
26	Egypt	Emerging Area	Egypt
27	SSA	Emerging Area	SSA
28	South Korea	Emerging Area	South Korea
29	ID and PH	Emerging Area	Indonesia and Phillippines
30	Other SEA excl PH	Emerging Area	Other SEA exclude Phillines
31
32

<tbody>
</tbody>

Caption 2 (Selecting Brazil):

Item	Area	Region	Sub Region/Comments
1	Brazil	Brazil	Brazil
16	Brazil	Brazil	Brazil
31
32

<tbody>
</tbody>

 

[Peter_SSs]

Thanks, that helps a bit but now I have more questions. Your example uses Brazil and in Caption 1, wherever Brazil occurs, it occurs in all 3 columns. Could you now post the expected result if Argentina had been chosen. I note, for example, that Argentina appears in all 3 columns in some rows but only 1 column in some rows. Trying to determine what the "rule" is for displaying a row.

Edit: On re-reading I think it is just the 'Area' column that you are interested in, but please confirm.

Is this a formal Excel table? If so, what is its name?

Where is the table located on the worksheet (ie what columns & rows)?

Is the Text Box on the worksheet itself? Is it a Forms Control Text Box or an ActiveX Text Box?
Could the selection just be made in a cell that has Data Validation set up in it?

 

[Cornstarch]

Peter, result if was picked Argentina:

Item	Area	Region	Sub Region/Comments
3	Argentina	Argentina	Argentina
18	Argentina	Argentina	Argentina
31
32

<colgroup><col width="64" style="width: 48pt;"> <col width="145" style="width: 109pt; mso-width-source: userset; mso-width-alt: 5302;"> <col width="98" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3584;"> <col width="182" style="width: 137pt; mso-width-source: userset; mso-width-alt: 6656;"> <tbody> </tbody>

Our function will only target Argentina in column B, "Area", and delete every other rows that aren't "Argentina" and "blank" (Keeping blanks is important as this table is linked to a pivot).

It is a formal excel table named "TableName"

This Table (as the exmple) is located from column A to D, Raw 2 to 33. But in the real table rows aren't always the same and columns may be added.
Would it be possible to cover all active columns and from 2nd up to the last raw?

Then About Selection:

S1
Text box on the worksheet itself, first asking if we need to "Split data by Area?"
"NO" will close the box and end function.
"YES" triggers the function to pick "Which Area do you want to keep?"

An other way,

S2
Would be to make a specific sheet, on which we cloud have the data validation the list of single Area's. Letting user Select the Area, which triggers VBA running the above logic and when done hide the sheet.

Thank you again for your time and efforts trying to help.

CornStarch

 

[Peter_SSs]

Our function will only target Argentina in column B, "Area", and delete every other rows that aren't "Argentina" and "blank"

Just checking ..
Delete the other rows, or just Hide them?

S2
Would be to make a specific sheet, on which we cloud have the data validation the list of single Area's. Letting user Select the Area, which triggers VBA running the above logic and when done hide the sheet.

Hide which sheet? The one with the Data validation?

 

[Cornstarch]

Hi Peter,

Yes pemanently delete others rows.

Hide the sheet letting user selecting area to keep.

Best,

J

 

[Peter_SSs]

OK, test this.
Data validation to choose the 'Area' is in cell A1 of a separate sheet - mine is called 'DV' but the name doesn't matter.
TableName is on a sheet called 'Data'. Both the table name and this sheet name are easily editable in the code.
To implement this code ..
1. Right click the 'DV' (or whatever) sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test by choosing a value in the Data Validation cell.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim sArea As String
  Dim r As Long
  Dim tbl As ListObject
  
  If Not Intersect(Target, Range("A1")) Is Nothing Then '<- Check DV cell address
    sArea = Range("A1").Value                           '<- Check DV cell address
    If Len(sArea) > 0 Then
      Set tbl = Sheets("Data").ListObjects("TableName") '<- Edit sheet & table name to suit
      For r = tbl.ListRows.Count To 1 Step -1
        If tbl.ListRows(r).Range.Cells(2).Value <> sArea And Not IsEmpty(tbl.ListRows(r).Range.Cells(2).Value) Then
          tbl.ListRows(r).Delete
        End If
      Next r
      tbl.Parent.Activate
      Me.Visible = xlSheetHidden
    End If
  End If
End Sub

 

[Cornstarch]

Amazing Peter, thanks a lot. This works

In case you stop in HK, let me know I owe you a treat!

Best,

J

 

[Peter_SSs]

Amazing Peter, thanks a lot. This works

You are welcome.

In case you stop in HK, let me know I owe you a treat!

Noted!!