Macro - hide rows based on a value in cells

[Darkshine]

Good afternoon,

I have a workbook that has 5 or more sheets.

The sheet I require this macro in is called "TRIAL"

The sheet contains a data dump from another software program and I would like to create a macro and make the following happen:

Look at column J AND column L - if there is no data in either row - hide the entire row PROVIDING ANY DATA IN COLUMN E OF THE SAME ROW DOESN'T CONTAIN DATA THAT IS IN BOLD


So, as an example, for row 1...

If column E contained data but it wasn't bold, and there was no data in J or L - hide
if column E contained BOLD data but there was no data in J or L - keep
if column E contained data but it wasn't bold, and there was data in J or L - keep

I would call the macro "condense"

Can that be done?

Thank you in advance.

 

[nemmi69]

Is there a column that will always have a value? This is so the last row with data can be found.

 

[nemmi69]

Option Explicit


Dim Rloop As Long


Sub condense()
'fixed range for now
For Rloop = 1 To 7
    If IsEmpty(Range("J" & Rloop).Value) And IsEmpty(Range("K" & Rloop).Value) Then
        If Range("E" & Rloop).Font.Bold = True Then
                Rows(Rloop & ":" & Rloop).EntireRow.Hidden = True
        End If
    End If
Next Rloop
End Sub

 

[JackDanIce]

Try:

Sub Condense()

Dim x as Long
Dim r as Range

Application.ScreenUpdating = False

With Sheets("TRIAL")
  On Error Resume Next
  If .AutoFilter then .AutoFilterMode =False
  On Error Goto 0

  x = .Cells(.Rows.Count, 5).End(xlup).Row
  With .Cells(1,1).Resize(x, 12)
    .AutoFilter
    .AutoFilter Field:=10, Criteria1:=""
    .AutoFilter Field:=12, Criteria1:=""
  End With
  For Each rng in .Cells(1, 5).Resize(x).SpecialCells(xlCellTypeVisible)
    If Not rng.Font.Bold Then rng.EntireRow.Hidden = True
  Next rng
  .AutoFilterMode = False
End With

Application.Screenupdating = True

End Sub

 

[Darkshine]

Is there a column that will always have a value? This is so the last row with data can be found.

Column B will always have a value at last

 

[Darkshine]

Try:

Sub Condense()

Dim x as Long
Dim r as Range

Application.ScreenUpdating = False

With Sheets("TRIAL")
  On Error Resume Next
  If .AutoFilter then .AutoFilterMode =False
  On Error Goto 0

  x = .Cells(.Rows.Count, 5).End(xlup).Row
  With .Cells(1,1).Resize(x, 12)
    .AutoFilter
    .AutoFilter Field:=10, Criteria1:=""
    .AutoFilter Field:=12, Criteria1:=""
  End With
  For Each rng in .Cells(1, 5).Resize(x).SpecialCells(xlCellTypeVisible)
    If Not rng.Font.Bold Then rng.EntireRow.Hidden = True
  Next rng
  .AutoFilterMode = False
End With

Application.Screenupdating = True

End Sub

Hi,

Where do I post it (I have placed it in the sheet on a general tab) in the VBA objects section

Having done that, how do i run it?

I have a button on the page which I can assign macro "Condense" to, or should it run as soon as data is placed on the sheet?

Thanks.

 

[JackDanIce]

Place the code into a standard module and then assign the macro to the button on the worksheet.

 

[Darkshine]

Thank you very much for your assistance folks! Job is done!

 

[JackDanIce]

Glad it's resolved