Borrar rápidamente el formato condicional de todas las hojas de Excel

Problema

Queremos borrar el formato condicional de todas las hojas de Excel. En lugar de ir hoja a hoja: Inicio, clic en Formato condicional > Borrar reglas > Borrar reglas de toda la hoja.

Solución

Una vez ejecutado el código no podremos deshacer los cambios. Por lo que es recomendable guardar una copia antes y salir sin guardar si no queremos preservar los cambios,

Sub delete_cond_format()
Application.ScreenUpdating = False
    For Each sht In Worksheets
     sht.Cells.FormatConditions.Delete
    Next
Application.ScreenUpdating = True
End Sub

Notas

Usamos la grabadora de macros mientras borramos el formato condicional de un rango de celdas.

Sub Macro1()
'
' Macro1 Macro
'
'
    Range("C5:E8").Select
    Cells.FormatConditions.Delete
End Sub

Después usamos ese código dentro de un bucle. Activo y desactivo la actualización de la pantalla, aunque es opcional.

Clear conditional formatting from all sheets at once in Excel

Problem

We want to delete all conditional formatting in all sheets at once. Instead of clearing the rules sheet by sheet: Home tab, click on Conditional Formatting > Clear Rules > Clear Rules from Entire Sheet.

Solution

You cannot undo the changes after running the code. Save a copy of the workbook and close without saving the changes if you don't want to preserve them.

Sub delete_cond_format()
Application.ScreenUpdating = False
    For Each sht In Worksheets
     sht.Cells.FormatConditions.Delete
    Next
Application.ScreenUpdating = True
End Sub

Notes

We just use the macro recorder while deleting the conditional formatting of a range of cells:

Sub Macro1()
'
' Macro1 Macro
'
'
    Range("C5:E8").Select
    Cells.FormatConditions.Delete
End Sub

Then we just include that code inside a loop to delete the format of all the sheets. I turn screen updating off and on but this is optional.

Aplying a function by row in R

Problem

We want to apply a function to each row of a data frame. In our example, we'd like to calculate the min and median for each row of a data frame.

df <- structure(list(V1 = c(5L, 4L, 7L), V2 = c(8L, 9L, 3L), V3 = c(12L, 
5L, 9L)), .Names = c("V1", "V2", "V3"), class = "data.frame", row.names = c(NA, 
-3L))

 V1 V2 V3
1  5  8 12
2  4  9  5
3  7  3  9

Solution

dplyr

library(dplyr)
# Using the pipe operator %>%
df %>% 
  rowwise() %>% 
  mutate(min = min(V1, V2, V3), median = median(c(V1, V2, V3)))
# Without %>%
mutate(rowwise(df), min = min(V1, V2, V3), median = median(c(V1, V2, V3)))

Source: local data frame [3 x 5]
Groups: 

     V1    V2    V3   min median
  (int) (int) (int) (int)  (int)
1     5     8    12     5      8
2     4     9     5     4      5
3     7     3     9     3      7

Base package

df$min <- apply(df, 1, min)
df$median <- apply(df[, 1:3], 1, median)

  V1 V2 V3 min median
1  5  8 12   5      8
2  4  9  5   4      5
3  7  3  9   3      7

References

• stackoverflow