Free Microsoft Excel 2013 Quick Reference

delete extra rows at bottom

I am trying to delete extra rows at the bottom of my worksheet because they're all empty but they're making the file size huge!

Here are the repro steps:

1. I have two columns in the first worksheet
a. day (mon-sun),
b. date (1-1-07 to 12-31-07)
(these take up 366 rows total)

2. I copied columns A and B and then I went to the second spreadsheet and went to "paste special" > "paste link".

3. Now, that second worksheet has 65536 rows instead of only 366. I tried deleting the extra rows, but it's not working...

How can I get rid of these extra rows?

I'd appreciate any help - these extra rows are killing the file size.


Post your answer or comment

comments powered by Disqus
Hi guys,

is there anyway to repeat rows at bottom?

or at least on the last page?

thanks in advance

I have a worksheet that has 156 rows in it, someone has scrolled down the sheet an it seems that they have inserted 45,000 empty rows. This has made my spread sheet size large. I have tried to delete the rows at the bottom of the sheet, been onto google and found some macros to run. The macros run ok and delete any empty rows if they are in the first 156 rows but they dont seem to delete all the empty rows at the bottom of the sheet.

I could cut and paste the worksheet but it is quite a complicated one with lots of formatting and formulas.

There must be a simple solution, can anyone help?

I have a large spreadsheet, and for some reason on one page, there are some
5000 extra rows at the bottom. There's nothing in them - I've selected them
all, cleared the contents, and asked Excel to delete them, but they won't go
away. They're making the spreadsheet even larger, and the page navigation
that much more difficult. Why won't they go away??

I have a spreadsheet that has extra cells at the bottom which are empty. They seem to go to infinity. How can I delete them. I didn't create the spreadsheet.

It looks like it just has a border that goes on forever (no data inside).

Please help.

Hi All,

These forums have been great for me however I can't seem to find a solution to my problem.

I have 3 salesman, each with their own proposal log.

Upon clicking a button (in excel) to create a proposal (using a merge from named ranges in excel to bookmarks in a word template), I want the macro to first open up the salesman's relevant proposal log and copy certain named ranges into a new row at the bottom of the log table.

I get no errors when running the code and the correct proposal log is opened (as well as the merge to the word document), however the range I want to copy and paste into the proposal log does not occur and I can't work out why.

Here is the code:


	VB:
	
 
    Dim wk As String 
    On Error Goto Notopen 
    wk = Workbooks(Book).Name 
    Workbookopen = True 
    Exit Function 
Notopen: 
    Workbookopen = False 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

	VB:
	
 BCMerge() 
    Dim pappWord As Object 
    Dim docWord As Object 
    Dim wb As Excel.Workbook 
    Dim Propwb As Excel.Workbook 
    Dim xlName As Excel.Name 
    Dim TodayDate As String 
    Dim Path As String 
    Dim YesOrNoAnswerToMessageBox As String 
    Dim QuestionToMessageBox As String 
    Dim Ph As String 
    Dim Sm As String 
     
     
    QuestionToMessageBox = "Are you sure you wish to create proposal?" 
     
    YesOrNoAnswerToMessageBox = MsgBox(QuestionToMessageBox, vbYesNo, "Create Proposal or Not") 
     
    If YesOrNoAnswerToMessageBox = vbNo Then 
         
    Else 
         
        Ph = Workbooks("Estimate Sheet7").Path 
        Sm = [salesman] 
         
        On Error Resume Next 
        If [salesman] = "AP" Then 
            If Not Workbookopen("AP Proposal Log.xlsm") Then 
                Workbooks.Open Filename:=Ph & "AP Proposal Log.xlsm", UpdateLinks:=0 
            End If 
             
        ElseIf [salesman] = "MB" Then 
            If Not Workbookopen("MB Proposal Log.xlsm") Then 
                Workbooks.Open Filename:=Ph & "MB Proposal Log.xlsm", UpdateLinks:=0 
            End If 
             
        ElseIf [salesman] = "JH" Then 
            If Not Workbookopen("JH Proposal Log.xlsm") Then 
                Workbooks.Open Filename:=Ph & "JH Proposal Log.xlsm", UpdateLinks:=0 
            End If 
        End If 
         
        Set Propwb = Workbooks(Sm & " Proposal Log.xlsm") 
         
        Windows("Estimate Sheet7").Activate 
        Sheets("Calc Sheet").Select 
        Range("AD1:AL1").Copy 
        Propwb.Activate 
        Sheets("Proposal Log").Select 
        Range("A2").End(xlDown).Select 
        ActiveCell.Offset(1, 0).Activate 
        Sheets("Proposal Log").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
        :=False, Transpose:=False 
        Application.CutCopyMode = False 
    End If 
     
    Set wb = ActiveWorkbook 
    TodayDate = Format(Date, "mmmm d, yyyy") 
    Path = wb.Path & "DROSS PRESS PROPOSAL - bookmark attempts 7" 
     
    On Error Goto ErrorHandler 
     
     'Create a new Word Session
    Set pappWord = CreateObject("Word.Application") 
     
    On Error Goto ErrorHandler 
     
     'Open document in word
    Set docWord = pappWord.Documents.Add(Path) 
     
     'Loop through names in the activeworkbook
    For Each xlName In wb.Names 
         'if xlName's name is existing in document then put the value in place of the bookmark
        If docWord.Bookmarks.Exists(xlName.Name) Then 
            docWord.Bookmarks(xlName.Name).Range.Text = Range(xlName.Value) 
        End If 
    Next xlName 
     
     'Activate word and display document
    With pappWord 
        .Visible = True 
        .ActiveWindow.WindowState = 0 
        .Activate 
    End With 
     
     'Release the Word object to save memory and exit macro
ErrorExit: 
    Set pappWord = Nothing 
    Exit Sub 
     
     'Error Handling routine
ErrorHandler: 
    If Err Then 
        MsgBox "Error No: " & Err.Number & "; There is a problem" 
        If Not pappWord Is Nothing Then 
            pappWord.Quit False 
        End If 
        Resume ErrorExit 
    End If 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help would be greatly appreciated.

Thanks.

Hello All,

I have a series of tables that I am importing from a website to my Excel spreadsheet. The website gives weather observations that are reported as much as 300 times per day. I need to get just hourly observations. The website does not offer an option to filter the table before displaying it so I have to import the entire table then eliminate the data I don't need. Not a problem except the process is taking about 6 seconds per cycle and I'm running over 100,000 cycles.

At least half the time is spent deleting extra rows of data that are between the hourly observations. I created a code loop that checks the observation time, then deletes all the rows between each hourly observation in 24 chunks.


	VB:
	
iHour = 0 
lStart = 65536 
lEnd = 65536 
 
For k = TopRow To LastRow - 1 
    If Cells(k, 2).Value = "" Then 
        Range(Cells(lStart, 2), Cells(lEnd, 2)).EntireRow.Delete 
        Exit For 
    ElseIf Cells(k, 2).Value >= iHour / 24 Then 
        Range(Cells(lStart, 2), Cells(lEnd, 2)).EntireRow.Delete 
        k = k - (lEnd - lStart) 
        If iHour  0 Then 
            iHour = Hour(Cells(k - 1, 2).Value) + 1 'iHour + 1
        Else 
            iHour = 1 
        End If 
        lStart = k 
Else: 
        lEnd = k 
    End If 
Next k 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I know deleting rows in Excel is a time consuming process, so I thought I might put the entire range into a variable and eliminate the unneeded data before pasting the variable back to the sheet. If I delete the data in some postions of the variable is there a fast way to eliminate those empty positions before writing back to the sheet?

This may not even be the best approach. I frequently take the long way around since I don't know all the options available. Any help would be appreciated.

Hi,
I've searched but can't find a suitable answer to this situation: how do you tell excel to repeat rows at the BOTTOM of each print page?
It's easy enough in page setup to get repeat rows at the top but I want to do exactly the same at the bottom.
The rows I want to reappear stay exactly the same (quantity, height etc) so it doesn't have to be too fancy!
I don't think it can be done at all in page setup - unless someone else knows something different...
Any ideas anyone?
Thanks in advance.

Hi - many thanks to anyone who can help with this!

I need to write a macro to add a new row at the bottom of a table, whilst at the same time copying down a selection of the row above it.

I am able to write a macro to add a new row in at a specific point and fill down the required section but I am not sure how to tell it to always add at the bottom of the dataset.

This is how it currently stands:

Rows("9:9").Select
Selection.Insert Shift:=xlDown
Range("K8:AH8").Select
Selection.AutoFill Destination:=Range("K8:AH9"), Type:=xlFillDefault
Range("K8:AH9").Select
Range("AH25").Select
ActiveWindow.SmallScroll Down:=-1
Range("B8:AH8").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With

Thanks very much to anyone who can help!

Is it possible to repeat rows at bottom of the page in the print out like rows to repeat at top..

My worksheet has about 66,000 empty rows at the bottom of the sheet and that
affects my usage of the scrollbar on the right of the screen. Anyone know how
i can get rid of those miscellaneous rows?

Hi,

Anyone have any idea's on how to add a range of cells to the end of everypage that prints?

Something like in page setup you can add rows to repeat at the top...but I want rows at the bottom to repeat?

My range would be range("A359:I377") On every page that prints.

Thank You, Mike

Hi.

I'm pretty green when it comes to macros, so hoping someone could help me out.

I want to check each number in the Number column of "OldData" sheet, and search "NewData" sheet to see if the number is present. If the number is NOT in the Number column of "NewData", then copy entire row that contains that number from "OldData" and paste at bottom of "NewData" sheet.

Is this very complicated?

So to try and summarize in steps:
1. Copy B2 of OldData sheet.
2. Find B2 (OldData) in NewData sheet column B.
3a. If found, move on to B3, loop back to step 1.
3b. If not found, copy Row 2, and paste in first empty row of NewData sheet. Move on to B3.

Big thanks in advance!

I have a worksheet that has unwanted blank rows that came with the export
information. I am trying to delete these rows at one time. I have 1810 lines
to go through and don't want to waste time going through deleting rows. Is
there an easy function to do this?

Thanks,
Gina

I have a named dynamic range for printing, ie AATableA which is defined
as =OFFSET('Table A Master'!$A$1,0,0,'TableA Input'!$B$160,18)
The part of the procedure below prints out only those rows which have
continuous entries and leave all the blank rows below. This works fine
but on row 160 there are totals for the columns already in the printout.
What I want to do ideally is to print out the completed rows as I do
already but somehow in the procedure add in the totals row at the bottom
of the printed rows. Is there some way to add this to the print area or
does it have to be re-written in another procedure.

ActiveSheet.PageSetup.PrintArea = "AATableA"
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$5"
.Orientation = xlLandscape
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
.LeftFooter = "&F"
End With
ActiveWindow.SelectedSheets.PrintOut

Grateful as always for any guidance.

Graham Haughs
Turriff
Scotland

Hello all,

Is there a way that you can delete multiple rows in one time.
I have an excel file from a LML file with more then 31000 rows and
on row 61, 122 etc, there needs to be deleted 10 rows.
Can somebody please give me a code to this with VB?

Any help will be great!

Gr. Wouter

See file for my question!

I have an excel sheet which has about 200 rows of data. When I pull the
scroll bar down, it goes down to row 385. These extra rows don't have data
in them. How can I set it so the scroll bar only pulls down to one row
after the last row of data?

--
Thanks!

Is there a way to delete extra rows and columns from a worksheet? I have all
the data I need in the sheet and would like to eliminate the file size by
getting rid of all the extra rows and columns.
Example:
The worksheet would be A1:F51, rows G on would be gone.
Thanks!

I exported data from a Crystal Report into Excel. During the export process
extra fields and and columns were added to the data set. I would like to
delete the blank rows that were inserted between every record from my
spreadsheet.

Is there a way to delete extra rows from a spreadsheet without selecting
each row? For example, could I run a script to delete rows with null values
or space?

Thanks

A simple macro to delete 1 row at a time when the value of a cell changes now deletes 250+ rows at a time. I only want to delete the 1st row of a worksheet when there is a change in a cell on the 1st row, then move the following row in its place.

Private Sub Worksheet_Change(ByVal Target As Range)

Range("1:1").Select
Selection.Delete xlShiftUp
End Sub

Thanks for any help!!

Hi,

I have this macro that I created (see attached). The macro creates the data on the 'chart sheet' by running the macro on the 'raw data sheet '. All is fine here however the row data on the raw data sheet can sometimes be shorter than the range specified in the macro which skews the data on the chart. need a way to update the macro to take the row data number provided every time rather than to manually delete the rows every time.
need help here..
thanks ..

Sub MainClearing()
'
' MainClearing Macro
'

'
    Columns("A:B").Select
    Selection.EntireColumn.Hidden = True
    Columns("D:K").Select
    Selection.EntireColumn.Hidden = True
    Columns("M:N").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("X2").Select
    Columns("L:L").EntireColumn.AutoFit
    Columns("C:S").Select
    Columns("C:S").EntireColumn.AutoFit
    Columns("D:K").Select
    Range("K1").Activate
    Selection.EntireColumn.Hidden = True
    Columns("C:M").Select
    Selection.ColumnWidth = 8.57
    Columns("E:E").Select
    Columns("D:D").ColumnWidth = 13.29
    Columns("C:C").ColumnWidth = 13
    Columns("D:K").Select
    Selection.EntireColumn.Hidden = True
    Columns("L:L").ColumnWidth = 13.86
    Columns("C:L").Select
    Selection.NumberFormat = "[h]:mm:ss"
    Columns("M:N").Select
    Selection.NumberFormat = "[h]:mm:ss"
    Columns("L:L").Select
    Selection.TextToColumns Destination:=Range("L1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    Range("M2").Select
    ActiveWindow.SmallScroll Down:=0
    Range("N2").Select
    ActiveCell.FormulaR1C1 = _
       
"=IF(RC[-11]<RC[-2],""Early"",IF(RC[-11]>RC[-1],""Late"",""On
Time""))"
    Range("N2").Select
    Columns("N:N").ColumnWidth = 23.43
    Columns("M:M").ColumnWidth = 15.29
    Range("N2").Select
    Selection.AutoFill Destination:=Range("N2:N254")
    Range("N2:N254").Select
    ActiveWindow.SmallScroll Down:=177
    ActiveWindow.ScrollRow = 177
    ActiveWindow.ScrollRow = 176
    ActiveWindow.ScrollRow = 175
    ActiveWindow.ScrollRow = 174
    ActiveWindow.ScrollRow = 172
    ActiveWindow.ScrollRow = 170
    ActiveWindow.ScrollRow = 160
    ActiveWindow.ScrollRow = 154
    ActiveWindow.ScrollRow = 150
    ActiveWindow.ScrollRow = 138
    ActiveWindow.ScrollRow = 131
    ActiveWindow.ScrollRow = 116
    ActiveWindow.ScrollRow = 107
    ActiveWindow.ScrollRow = 92
    ActiveWindow.ScrollRow = 86
    ActiveWindow.ScrollRow = 75
    ActiveWindow.ScrollRow = 69
    ActiveWindow.ScrollRow = 63
    ActiveWindow.ScrollRow = 60
    ActiveWindow.ScrollRow = 58
    ActiveWindow.ScrollRow = 54
    ActiveWindow.ScrollRow = 50
    ActiveWindow.ScrollRow = 42
    ActiveWindow.ScrollRow = 39
    ActiveWindow.ScrollRow = 29
    ActiveWindow.ScrollRow = 24
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
    Columns("N:N").Select
    Selection.FormatConditions.Add Type:=xlTextString, String:="Late", _
        TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlTextString, String:="Early", _
        TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16751204
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 10284031
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("N3").Select
    ActiveWindow.SmallScroll Down:=-24
    Range("N2").Select
    Sheets("Sheet2").Select
    ActiveCell.FormulaR1C1 = "Late"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "Early"
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "On Time"
    Range("A5").Select
    ActiveCell.FormulaR1C1 = "Total"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(Sheet1!C[12],""Late"")"
    Range("B1").Select
    Selection.AutoFill Destination:=Range("B1:B3"), Type:=xlFillDefault
    Range("B1:B3").Select
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(Sheet1!C[12],""Early"")"
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(Sheet1!C[12],""On Tim"")"
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(Sheet1!C[12],""On Time"")"
    Range("B1:B5").Select
    Range("B5").Activate
    ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]/R5C[-1]"
    Range("C1").Select
    Selection.AutoFill Destination:=Range("C1:C3")
    Range("C1:C3").Select
    Selection.Style = "Percent"
    Range("C1:C5").Select
    Range("C5").Activate
    ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"
    Range("A1:C5").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Font.Bold = True
    Range("A1:C3").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("'Sheet2'!$A$1:$C$3")
    ActiveChart.ChartType = xl3DColumnClustered
    ActiveChart.PlotBy = xlRows
    ActiveChart.Axes(xlCategory).Select
    ActiveChart.ChartType = xlCylinderColClustered
    ActiveChart.Axes(xlCategory).Select
    Selection.Delete
    ActiveSheet.ChartObjects("Chart 1").Activate
    Range("K11").Select
End Sub


I cant get my macro to find a set of data, skip that row and the next row. Any rows that dont contain my data I want to delete that row and the next row.

Option Explicit
Dim lRow As Long

Sub aaa()
With Sheets(4)
    For lRow = Range("C1").End(xlDown).Row To 2 Step -1
        If Left(Cells(lRow, "C").Value, 4) = "TA(M" Then
            Rows(lRow).Select    'visual aid
        Else
            Rows(lRow).EntireRow.Delete xlUp
        End If
    Next lRow
End With
End Sub
The above code deletes far to many rows.

Sample data:
See attached excel file (v2000).
Text in Green I want to keep, text in red I want to delete (row)

excel 2003 user here, trying to assist a 2007 user; and this is our problem:

we have a spreadsheet that is 1,048,544 Rows by XFD columns.

now we dont know how this happened as the phone rang and the computer was vacated, upon return we found that there were all these rows.

now initially there were values in the B column all the way from row 1 to row 1xx,xxx,xxx. we have deleted the values but can not delete the rows or columns (multiple nor singular fashions)

I found a thread on here that helped me "hide" these rows and columns, but still having the problem described below.

this creates a problem because it makes the computer creep, takes up a great deal of Ram and CPU speed. the file takes 4+ minutes to load or save.

please help!!
TIA

i currently have a recorded marco that inserts four rows, highlights and copies a different four rows and pastes it (while deleting their values) in the rows that were inserted.

the problem is that when i want to run the macro again, the four new rows that are inserted are in between the original four rows and the four rows that was pasted the first time the macro was ran.

i'd like to have a macro that will paste the rows at the bottom of the list.

the code currently look like:


	VB:
	
 InsertRow() 
     
    InsertRow Macro 
     
    Rows("18:21").Select 
    Selection.Insert Shift:=xlDown 
    Rows("14:17").Select 
    Range("C14").Activate 
    Selection.Copy 
    Rows("18:21").Select 
    ActiveSheet.Paste 
    Range("J18").Select 
    Application.CutCopyMode = False 
    ActiveCell.FormulaR1C1 = "" 
    Range("J21").Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help would be greatly appreciated.
Reply With Quote

Sometimes when I am working in excel I unintentionally create extra rows that
have nothing in them on the sides and at the bottom of my worksheet. I think
I do this by scrolling over further than I need or scrolling down further
than I want the screen to go. But then I cannot go and delete those extra
rows at all. They are just out there without anything in them. Is there a way
I can either delete those or only allow someone who uses my worksheet to see
the rows and columns I want them to see when they open it up?


No luck finding an answer? You could always try Google.