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

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 Jul 27 '20 at 20:53
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.

2
  • Do you have any VBA code for doing this? I have to do all this thing using just a single button – nyeas001 Jul 28 '20 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 Aug 4 '20 at 17:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.