Somehow getting extra blank rows with borders

NoSparks · #1 09-19-2018, 12:35 PM

Excel doesn't automatically reset the used range until the workbook is saved.

This is untested but you might get a reset on the fly with one of these two instructions right ahead of putting in the boarders and fonts.

Code:

    ActiveSheet.UsedRange
    X = ActiveSheet.UsedRange.Rows.Count
'Put borders around new rows.

kevinbradley57 · #2 09-19-2018, 12:45 PM

NoSparks - Thanks for the quick response, but neither of those instructions worked for me.

kevinbradley57 · #3 09-19-2018, 01:01 PM

NoSparks -- Your comment inspired me to look for (and find) code that resets the used range when data is copied from another source (that last bit is important). This works:

Code:

Sub Delete_Empty_Row()
    Application.ScreenUpdating = False
    For Each usedrng In ActiveSheet.UsedRange
        If usedrng.MergeCells = True Then
            If usedrng.Value = "" Then
                usedrng.Value = ""
            End If
        Else
            If usedrng.Value = "" Then
                usedrng.ClearContents
            End If
        End If
    Next
    
    ActiveSheet.UsedRange
    usedRangeLastColNum = ActiveSheet.UsedRange.Columns.Count
    usedrangelastrow = ActiveSheet.UsedRange.Rows.Count
    
    For r = usedrangelastrow To 1 Step -1
        If Application.WorksheetFunction.CountA(Cells(r, usedRangeLastColNum).EntireRow) <> 0 Then
            Exit For
        Else
            Cells(r, usedRangeLastColNum).EntireRow.Delete
        End If
    Next r
    
    For c = usedRangeLastColNum To 1 Step -1
        If Application.WorksheetFunction.CountA(Cells(1, c).EntireColumn) <> 0 Then
            Exit For
        Else
            Cells(1, c).EntireColumn.Delete
        End If
    Next c
    
    ActiveSheet.UsedRange
    Application.ScreenUpdating = True
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Template with background colors has extra blank page that will not delete.	jspinelli27	Word	1	03-11-2018 02:27 PM
Section Break Inserts an Extra Blank Page	Nathan8752	Word	3	12-09-2015 07:03 AM
Delete blank rows between the two rows that contain data	beginner	Excel Programming	5	12-26-2014 12:29 AM
Extra lines in directroy when field is blank	redzan	Mail Merge	5	05-23-2014 06:40 PM
Count rows and add blank rows accordingly	Hoochtheseal	Word VBA	1	01-29-2013 09:23 PM