row values 1

[matrixknow]

I have a sub that delete an empty row on basic of the value of the first cell. However this is not enough. He should dedect the values of the row or at least of a range (from the first cell to the cell of the last column. If I write this "IsEmpty(sh.Range(Cells(i4Row, 1), Cells(i4Row, 3))) it" is not working

  For i4Row = lLR To 2 Step -1
'      If sh.Cells(i4Row, "A").value = vbNullString Then
'        sh.rows(i4Row).EntireRow.Delete 'works but on one cell
'      End If
'   Next i4Row
   
    For i4Row = lLR To 2 Step -1
      If IsEmpty(sh.Range(Cells(i4Row, 1), Cells(i4Row, 3))) Then

        sh.rows(i4Row).EntireRow.Delete
      End If

 

[Fenrirshowl]

I think you are missing a Range function within your IsEmpty function i.e.

If IsEmpty([b]Range[/b](sh.Range(Cells(i4Row, 1), Cells(i4Row, 3)))) Then

I've not used IsEmpty but reading the help file it appears to work with variables rather than ranges (anyone out there care to comment/put me right?). Personally I'd be inclined to use something along the lines of:

Sub isempt()
For x = 1 To 6
If Application.WorksheetFunction.CountA(Range(Cells(x, 1), Cells(x, 6))) = 0 Then
    Rows(x).EntireRow.Delete
End If
Next x
End Sub

 

[WinblowsME]

Sub Delete_Empty_Rows()
   Dim i As Long, last_row As Long, last_col As Long
   
   last_row = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
   last_col = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column

   For i = last_row To 1 Step -1
      If Row_Is_Empty(i, last_col) Then
         Range(i & ":" & i).EntireRow.Delete
      End If
   Next i
End Sub

Private Function Row_Is_Empty(i As Long, last_col) As Boolean
   Dim j As Integer, is_empty As Boolean
   
   is_empty = True
   
   For j = 1 To last_col
      If Trim(Cells(i, j)) <> "" Then
         is_empty = False
         Exit For
      End If
   Next j
   
   Row_Is_Empty = is_empty
End Function

 

[matrixknow]

thanks WinblowsME for you code. I modified it a bit to my needs. I did a little test, it seems that is works. There is just one doubt, I tested with a msgbox and sometimes it looks het could a cell that is empty as a full cell.

Public Function DelBlankRows(WorkSheetName As String)

   Dim sh As Worksheet
   Dim lLastRow As Long
   Dim lLastCol As Long
   Dim iRowNr As Long
   
   lLastRow = 0
   lLastCol = 0
   iRowNr = 0

   Set sh = Worksheets(WorkSheetName)
   lLastRow = 0
   lLastCol = 0
   iRowNr = 0
   lLastRow = sh.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
   'MsgBox lLastRow
   lLastCol = sh.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
   'MsgBox lLastCol
   
   For iRowNr = lLastRow To 3 Step -1
        If Row_Is_Empty(iRowNr, lLastCol, sh) Then
        sh.rows(iRowNr).EntireRow.Delete
        'MsgBox "deleted row :" & iRowNr
      End If
   Next iRowNr
   Set sh = Nothing
End Function

Private Function Row_Is_Empty(iRowNr As Long, lLastCol, sh As Worksheet) As Boolean

   Dim j As Integer
   Dim is_empty As Boolean
   Dim teller As Integer
   teller = 0
      
   For j = 1 To lLastCol
      If sh.Cells(iRowNr, j).value <> vbNullString Then
         teller = teller + 1
         'MsgBox "Rownumber : " & iRowNr & " columnnr : " & j & " filledcell : " & teller
      Else
      teller = teller
      End If
   Next j
   
   If teller = 0 Then
   is_empty = True
   Else
   is_empty = False
   End If
   
   Row_Is_Empty = is_empty
   is_empty = False
   teller = 0
End Function

 

[WinblowsME]

If a cell contains only whitespaces, the cell is not empty. Play with the Trim and Replace functions.

   curr_cell = Trim ( Replace ( curr_cell, chr(10), "" ) )

 

[matrixknow]

 Dim curr_cell As String  
      
   For j = 1 To lLastCol
         curr_cell = Trim(Replace(sh.Cells(iRowNr, j).value, Chr(10), ""))
   
      If curr_cell <> vbNullString Then
         teller = teller + 1
        'test sh.Cells(iRowNr, j).Interior.Color = vbYellow

thanks again, this was the missing link to produce a 100% correct output