Delete Rows from a Range

[Designer6]

Hi All,

I would like some assistance with coding for this logic

Delete Rows based on empty cells with a range
Select Row Range from Row 11 to row 254
If detect 1 checkbox is selected // have this part
Enable that column, let's say column P or another words column 17 // have this part
Begin to check for empty cells starting from cell 11 go down within that column (column P)
If detect empty cells remove the rows the associated rows

If detect more than 1 checkbox is selected //have this part
Enable the columns that associated with those checkboxes, let's say columns P,Q,R or another words column 17,18, 19 // have this part
Begin to check for empty cells begin with the first column (colum17) go across.
For multiple columns selected, remove rows when all cell is blank. In this case it has to be three empty cells. If any of the three cells has data skip to the next row and repeat the same process

This logic for 20-30 columns

Right now the code for my checkboxes are true statements

If checkbox(1) = True Then
"execute codes"
If checkbox(2) = True Then
"excecute codes"
If checkbx(3) = True Then
" execute codes"
..Any suggestions for improvement for this section of code ?


This logic for 20-30 columns.

Many Thanks,
V

[Oorang]

Hi D,
Welcome to the board This is a little rough but I think this will do what you described:
[VBA]Option Explicit

Public Sub TestDelete()
DeleteRowsInRange ActiveSheet, 1, 20, 2, 3
End Sub

Private Sub DeleteRowsInRange(ws As Excel.Worksheet, topRow As Long, bottomRow As Long, ParamArray columnNumbers() As Variant)
Dim lngRow As Long
Dim lngIndx As Long
Dim strVal() As String
Dim lngLwrBnd As Long
Dim lngUprBnd As Long
lngLwrBnd = LBound(columnNumbers)
lngUprBnd = UBound(columnNumbers)
'Working from bottom up prevents skipping rows:
For lngRow = bottomRow To topRow Step -1
For lngIndx = lngLwrBnd To lngUprBnd
If LenB(ws.Cells(lngRow, columnNumbers(lngIndx)).Formula) Then
Exit For
End If
Next
If lngIndx > lngUprBnd Then
'All checked cells were empty:
ws.Rows(lngRow).Delete
End If
Next
End Sub
[/VBA]

[Designer6]

Thanks for the reply. I haven't tried the code yet. However i have a question

what does this subroutine do and especially the # 20?

Public Sub TestDelete() 
    DeleteRowsInRange ActiveSheet, 1, 20, 2, 3 
End Sub

[Oorang]

This is saying "Examine rows 1 through 20 in the ActiveSheet and if anyrow (1 through 20) has a blank cell in all off the columns I specify (in this case 2 and 3) then delete that row".

[Designer6]

Hello Guys,

The previous title didn't describe what i really wanted the macros to do. Instead of deleting the rows i would like to hide them. After the columns are successfully unhidden i would like the next piece of code to hide the rows base on empty cells in each column. For example, If column A, D, F, and G are unhidden i would like to hide the rows of those columns, starting from row 11 to row 254 of each colum if they're all blank/empty. If any of the columns has data do not hide that row skip to the next row


Here is the link to find then unhide Thread
http://www.vbaexpress.com/forum/show...ide+empty+rows


Thanks,
Designer6

[Designer6]

I have the code for hiding the rows base on empty columns. However, the code only looks at numbers like 1, 2, 3, 4, 5 etc. Could anyone suggest me a way to modify this code so it looks for characters like A, B, C, O etc.. instead of number/value base.

Sub HideEmptyRows()

      Dim HiddenRow&, RowRange As Range, RowRangeValue&

      '*****************************
      '< Set the 1st & last rows to be hidden >
      Const FirstRow As Long = 11
      Const LastRow As Long = 254

      '< Set your columns that contain data >
      Const FirstCol As String = "P"
      Const LastCol As String = "AV"
      '*****************************
      
      ActiveWindow.DisplayZeros = False
      Application.ScreenUpdating = False

      For HiddenRow = FirstRow To LastRow

            '(we're using columns P to AV here)
            Set RowRange = Range(FirstCol & HiddenRow & _
                                 ":" & LastCol & HiddenRow)

            'sums the entries in cells in the RowRange
            RowRangeValue = Application.Sum(RowRange.Value)

            If RowRangeValue <> 0 Then
                  'there's something in this row - don't hide
                  Rows(HiddenRow).EntireRow.Hidden = False
            Else
                  'there's nothing in this row yet - hide it
                  Rows(HiddenRow).EntireRow.Hidden = True
            End If

      Next HiddenRow

      Application.ScreenUpdating = True

End Sub

[Designer6]

I know you need to change this part of the code to string or another of doing it is to check the content of a cell if it's empty delete. Could anyone help me out ?

'sums the entries in cells in the RowRange
            RowRangeValue = Application.Sum(RowRange.Value)

            If RowRangeValue <> 0 Then
                  'there's something in this row - don't hide
                  Rows(HiddenRow).EntireRow.Hidden = False
            Else

[mdmackillop]

Can you post a sample and indicate the rows you want hidden?