0

Lets say I have 10 groups of data sets.

I want to compare cell values of "E" and "G" for group 1 data set. If "E"cell value="G" cell value, I want to clear whole row content, but not the formulas of that rows. I want to use the same file to calculate the result for group 2-10 number data set.

The problem I am having is that, my code clears the content and formulas both. So, I cannot reuse the file for group 2 data set, the formatting becomes a permanent thing.

There should have a way so that I can keep the formulas after every run of macros so that when I enter new data set, I can calculate the result.

To sum up, I need a VBA code so that, If two cell values are matched, whole row content will be cleared but formulas will be there so that for the next group of data, I can use the same excel template

The code I am using is as follows:

Sub ClearAllButFormulas() Worksheets("Calculation").Activate Dim N As Long, i As Long N = Cells(Rows.Count, "E").End(xlUp).Row For i = N To 2 Step -1 If Cells(i, "E").Value = Cells(i, "G").Value Then Cells(i, "E").EntireRow. ClearContents End If Next i End Sub

Before formatting After formatting

Improve this question
1
  • Can you instead use an extra column that returns whether or not E and G are equal, and then sort/filter by that column?
    – JSmart523
    Commented Jul 27, 2020 at 20:53

2 Answers 2

Reset to default
1

Use Tables.

Consider a table of

Fruit   Color
Bananas Yellow
Apples  Red

Highlight all six cells and click "Insert - Table" from the ribbon.

Then in the column after color, you can add a formula of

[@Fruit] & " are " & LOWER([@Color]) & "."

and as long as the entire column is the same formula (e.g. you don't edit that column to manually add values instead of changing the formula) then the formula will be automatically copied down the entire column. Internally, the formula will be stored once per column regardless of how many rows are in the table!

Cheat sheet for table-based formulas:

  • [Column] refers to all data cells in that column.
  • [[Column Title]] is the same thing except, for some reason, spaces in column titles require the additional square brackets. Don't worry... just click and Excel will figure it out.
  • [@Column] refers to the cell value in the "Column" column for the current row.

So if you have a table with a column of "Cost", then in another column you could have an "Is This Row's Cost Above Average" column with a formula of:

=[@Cost]>AVERAGE([Cost])

To get a value from the previous or next row, you can't use cell references without disengaging the "one formula for the entire column" magic. Intead, use OFFSET(). A column to see if the cost is greater than the previous would be something like:

=IFERROR([@Cost]>OFFSET([@Cost],-1,0), "")

Now as long as you don't remove all of the rows in the table, you can remove any of them, and if you add any back the formulas will automatically copy down.

Improve this answer
2
  • Do you have any VBA code for doing this? I have to do all this thing using just a single button
    – nyeas001
    Commented Jul 28, 2020 at 15:23
  • No, I don't. If you go this route, I suggest starting with looking at example code provided here and here
    – JSmart523
    Commented Aug 4, 2020 at 17:51
0

You could do an iteration based on the amount of rows with a conditional to check for the values..

Code is working for Excel 2016 VBA - For example

For counter = 1 To 10


    If Range("E" & counter).Value = Range("G" & counter).Value Then
                    
                        Rows(counter & ":" & counter).Clearcontents
                        
                        
                    End If

Next counter
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .