VBA raw eraser given specified column cell Value(s)

C

Cornstarch

New Member
Joined
Jan 17, 2018
Messages
7
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
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.
 
Upvote 0
Very well Peter, thanks informing.

Correction bellow,

Caption 1 (Starting table):

[TABLE="class: grid, width: 368"]
<tbody>[TR]
[TD="width: 64"]Item
[/TD]
[TD="width: 145"]Area
[/TD]
[TD="width: 98"]Region
[/TD]
[TD="width: 182"]Sub Region/Comments
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Brazil
[/TD]
[TD]Brazil
[/TD]
[TD]Brazil
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2
[/TD]
[TD="bgcolor: transparent"]EMME
[/TD]
[TD="bgcolor: transparent"]EMME
[/TD]
[TD]Whole EMME
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Argentina
[/TD]
[TD]Argentina
[/TD]
[TD]Argentina
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]4
[/TD]
[TD="bgcolor: transparent"]Bolivia
[/TD]
[TD="bgcolor: transparent"]Argentina
[/TD]
[TD="bgcolor: transparent"]Bolivia
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Chile
[/TD]
[TD]LAN
[/TD]
[TD]Chile
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]6
[/TD]
[TD="bgcolor: transparent"]Colombia
[/TD]
[TD="bgcolor: transparent"]LAN
[/TD]
[TD="bgcolor: transparent"]Colombia
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]CAM
[/TD]
[TD]LAN
[/TD]
[TD]CAM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]8
[/TD]
[TD="bgcolor: transparent"]VEP
[/TD]
[TD="bgcolor: transparent"]LAN
[/TD]
[TD="bgcolor: transparent"]VEP
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]USA
[/TD]
[TD]NAFTA
[/TD]
[TD]USA
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]10
[/TD]
[TD="bgcolor: transparent"]Mexico
[/TD]
[TD="bgcolor: transparent"]NAFTA
[/TD]
[TD="bgcolor: transparent"]Mexico
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]Egypt
[/TD]
[TD]Emerging Area
[/TD]
[TD]Egypt
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]12
[/TD]
[TD="bgcolor: transparent"]SSA
[/TD]
[TD="bgcolor: transparent"]Emerging Area
[/TD]
[TD="bgcolor: transparent"]SSA
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]South Korea
[/TD]
[TD]Emerging Area
[/TD]
[TD]South Korea
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]14
[/TD]
[TD="bgcolor: transparent"]ID and PH
[/TD]
[TD="bgcolor: transparent"]Emerging Area
[/TD]
[TD]Indonesia and Phillippines
[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]Other SEA excl PH
[/TD]
[TD]Emerging Area
[/TD]
[TD]Other SEA exclude Phillines
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]16
[/TD]
[TD="bgcolor: transparent"]Brazil
[/TD]
[TD="bgcolor: transparent"]Brazil
[/TD]
[TD="bgcolor: transparent"]Brazil
[/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD]EMME
[/TD]
[TD]EMME
[/TD]
[TD]Whole EMME
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]18
[/TD]
[TD="bgcolor: transparent"]Argentina
[/TD]
[TD="bgcolor: transparent"]Argentina
[/TD]
[TD="bgcolor: transparent"]Argentina
[/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD]Bolivia
[/TD]
[TD]Argentina
[/TD]
[TD]Bolivia
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]20
[/TD]
[TD="bgcolor: transparent"]Chile
[/TD]
[TD="bgcolor: transparent"]LAN
[/TD]
[TD="bgcolor: transparent"]Chile
[/TD]
[/TR]
[TR]
[TD]21
[/TD]
[TD]Colombia
[/TD]
[TD]LAN
[/TD]
[TD]Colombia
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]22
[/TD]
[TD="bgcolor: transparent"]CAM
[/TD]
[TD="bgcolor: transparent"]LAN
[/TD]
[TD="bgcolor: transparent"]CAM
[/TD]
[/TR]
[TR]
[TD]23
[/TD]
[TD]VEP
[/TD]
[TD]LAN
[/TD]
[TD]VEP
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]24
[/TD]
[TD="bgcolor: transparent"]USA
[/TD]
[TD="bgcolor: transparent"]NAFTA
[/TD]
[TD="bgcolor: transparent"]USA
[/TD]
[/TR]
[TR]
[TD]25
[/TD]
[TD]Mexico
[/TD]
[TD]NAFTA
[/TD]
[TD]Mexico
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]26
[/TD]
[TD="bgcolor: transparent"]Egypt
[/TD]
[TD="bgcolor: transparent"]Emerging Area
[/TD]
[TD="bgcolor: transparent"]Egypt
[/TD]
[/TR]
[TR]
[TD]27
[/TD]
[TD]SSA
[/TD]
[TD]Emerging Area
[/TD]
[TD]SSA
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]28
[/TD]
[TD="bgcolor: transparent"]South Korea
[/TD]
[TD="bgcolor: transparent"]Emerging Area
[/TD]
[TD="bgcolor: transparent"]South Korea
[/TD]
[/TR]
[TR]
[TD]29
[/TD]
[TD]ID and PH
[/TD]
[TD]Emerging Area
[/TD]
[TD]Indonesia and Phillippines
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]30
[/TD]
[TD="bgcolor: transparent"]Other SEA excl PH
[/TD]
[TD="bgcolor: transparent"]Emerging Area
[/TD]
[TD]Other SEA exclude Phillines
[/TD]
[/TR]
[TR]
[TD]31
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]32
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

Caption 2 (Selecting Brazil):

[TABLE="class: grid, width: 288"]
<tbody>[TR]
[TD="width: 64, bgcolor: #5B9BD5"]Item
[/TD]
[TD="width: 106, bgcolor: #5B9BD5"]Area
[/TD]
[TD="width: 106, bgcolor: #5B9BD5"]Region
[/TD]
[TD="width: 106, bgcolor: #5B9BD5"]Sub Region/Comments

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DDEBF7"]1
[/TD]
[TD="bgcolor: #DDEBF7"]Brazil
[/TD]
[TD="bgcolor: #DDEBF7"]Brazil
[/TD]
[TD="bgcolor: #DDEBF7"]Brazil
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]16
[/TD]
[TD="bgcolor: transparent"]Brazil
[/TD]
[TD="bgcolor: transparent"]Brazil
[/TD]
[TD="bgcolor: transparent"]Brazil
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DDEBF7"]31
[/TD]
[TD="bgcolor: #DDEBF7"][/TD]
[TD="bgcolor: #DDEBF7"][/TD]
[TD="bgcolor: #DDEBF7"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]32
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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?
 
Last edited:
Upvote 0
Peter, result if was picked Argentina:

[TABLE="class: grid, width: 368"]
<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>[TR]
[TD="width: 64"]Item
[/TD]
[TD="width: 145"]Area[/TD]
[TD="width: 98"]Region [/TD]
[TD="width: 182"]Sub Region/Comments[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Argentina[/TD]
[TD]Argentina[/TD]
[TD]Argentina[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]18[/TD]
[TD="bgcolor: transparent"]Argentina[/TD]
[TD="bgcolor: transparent"]Argentina[/TD]
[TD="bgcolor: transparent"]Argentina[/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]32[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]
[/TD]
[/TR]
</tbody>[/TABLE]

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
 
Upvote 0
Cornstarch said:
Our function will only target Argentina in column B, "Area", and delete every other rows that aren't "Argentina" and "blank"
Click to expand...
Just checking ..
Delete the other rows, or just Hide them?

Cornstarch said:
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.
Click to expand...
Hide which sheet? The one with the Data validation?
 
Upvote 0
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).

Rich (BB code): 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
Code for rows of cells returning reverse-order values.
Replies
5
Views
452
Worksong
W
M
  • Question Question
VBA that pulls data based on multiple criteria
Replies
0
Views
339
Mouse7681
M
F
  • Question Question
VBA to take a field value from 1 worksheet and update that field value to another worksheet based on another cell value
Replies
4
Views
495
Peter_SSs
Peter_SSs
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
M
  • Question Question
Creating Results Table + Hiding columns based on text in specific row
Replies
4
Views
778
MelissaBr
M

Forum statistics

Threads
1,225,521
Messages
6,185,457
Members
453,292
Latest member
Michandra02

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top