Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Delete all rows in a spreadsheet except the first row

Hi I need to clear all rows in a spreadsheet except the top row. this will be a macro that is run automatically when the spreadsheet is closed.
i imagine i would need to find the last row in the spreadsheet and store as a variable though i dont know how to do this.
i guess it would just then require a range selected from cell A2 to I&lastrow (the spreadsheet will only be I columns wide)

any help would be greatly appreciated i dont have any code for this but i dont think it would require much.

thanks


Post your answer or comment

comments powered by Disqus
I have this macro to creat a sheet named "XYZ" and then adds all sheets within the workbook. However, I do not want to include a sheet named "ABC".

Combine all sheets in a workbook except the sheet named "ABC". is my question. Can you help me on this??

Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "XYZ"

For J = 2 To Sheets.Count
With Sheets(J).UsedRange
.Resize(.Rows.Count).Copy _
Sheets(1).Range("A65536").End(xlUp)(3)
End With
Next

Application.DisplayAlerts = False

I would like to write a macro that deletes all worksheets in a workbook
except for two, called "Buttons" & "Data". The problem is that the amount of
and names of the worksheets won't be static, so I can't simply write
"Worksheets ("Sheet1").Delete". I thank you in advance for your help.

i have a table(range of ceels that have been named) i have nemed the cell range as receipt and when a certain macro is run a new row is inserted in the middle of the table so the range name covers the inserted row, i would like to know a macro that deletes all the rows from thsi table except the first and last rwon in the cell range.

Hi, I try to use below code to clear all content and formatting of a worksheet except keeping the first row. It works successfully but as max no of rows in Excel 2007 onwards is 1,048,576 while Excel 2003 or below is 65,536 , seems it cannot cater all scenarios. Any suggestion? Thanks!

Sub clearExcelContent()

Rows("2:65536").Select
Selection.Clear
Range("A2").Select

End Sub

Hi
I'm sure there must be a quick way to highlight all cells in a spreadsheet, except say 10 rows and 10 columns

I want to do this so, that the majority of cells can be hidden (I am creating a Data Capture Form, which will have some cells protected, and some cells available for validated data entry)

At the moment I can't figure out a quick way to do this, other than highlighting a row and holding the mouse down, so that it trawls throught the 65,500+ rows!

Please help
Thanx
Tracy

Could someone please give me a macro or formula that deletes all of the words in a cell after the first word in the cell. Thanks

example

Dogs run fast
changes to
Dogs

How can I delete all blank rows in a spreadsheet without sorting the data as I want it to be in the exact order it is in.

How do I delete entire rows in a spreadsheet with a VBA macro?

I can't use autofilter (or similar techniques) as not all of the columns
have data in every non blank row i.e. if I sorted by column A, there may be a
row with a blank cell in column A, but another column (say AX) that may be
out of view could have data in it.

I cant seem to find the code from anywhere so hopefully somebody can help me. All i want to do is delete all records in a table using vba from excel

The code below opens up a table and inserts the data from excel
im db As Database, rs As Recordset, r As Long
    Dim EmpyRecord As Boolean

    Set db = OpenDatabase("H:maginusDatabaseReportingRebatesSell Out or In Allowance" & filename &
".mdb")
    ' open the database
    Set rs = db.OpenRecordset("SOA", dbOpenTable)
    ' get all records in a table
    'the start row in the worksheet
    r = 2

****** Here is where i want to delete the SOA table records*******
    
    Do While Len(Range("A" & r).Formula) > 0
    ' repeat until first empty cell in column A
        With rs
        
                Do While Len(Range("A" & r).Formula) > 0
                   
                    .AddNew ' create a new record
                    ' add values to each field in the record
                    .Fields("Customer Account") = Range("A" & r).Value
                    .Fields("Customer Name") = Range("B" & r).Value
                    .Fields("Product Code") = Range("C" & r).Value
                    .Fields("Product") = Range("D" & r).Value
                    .Fields("Quantity") = Range("E" & r).Value
                    .Fields("Claim") = Range("F" & r).Value
                    .Fields("Date") = Range("G" & r).Value
                    .Update ' stores the new record
                    r = r + 1 ' next row
                    
                    Loop
                Exit Do
                        
        End With
        r = r + 1 ' next row
    Loop
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing

    MsgBox ("Export to Access Complete")
Ive included the area where i want to delete the records in the above code. As in theory, it will easier to update the table by just deleting all the records and resubmitting all the data again.

Anyone know how to do this?

Many Thanks

Dave

I want to be able to delete all worksheets in a workbook except for the one labeled "Original" - I think I will need to loop through and test the name and delete the sheet if not named "Original". I am good with doing loops through Ranges but I cannot think what the syntax would be for looping through sheets.

Hi,

I Need a Macro To Clear All Content (Not Delete Rows) in a Column Except a List.

List Data
America
Africa
India
Japan

Work Sheet Data
India
America
England
Africa
China
Japan

As per List England and China Not Exist. Hence This Cell Should Be Cleared. It Should come like this
India
America

Africa

Japan

Thanks in Advance.

I want to find code that will delete all sheets in a workbook except one called "Original". How would I do that? I have no idea where to begin because I do not know what the names may be.

How can I delete all data in a database (Table Name: CUSTOMERID) by using VBA.

how do i add every fifth row in a spreadsheet?

Hi,

Is there a way to delete all sheets in a workbook after a certain period, say a month? Any idea? Thanks.

Langwo

Want to know how to Delete All Files in a folder....
lets say folder name is C:TEMP

TIA - Mike

*** Sent via Developersdex http://www.developersdex.com ***

Good afternoon everyone.
I have yet another problem that is doing my head in.
I am trying to programmatically delete all worksheets in a workbook except
the first one.
I have tried the following:

Dim p As Integer
Dim q As Integer
p = Worksheets.Count
If p >= 2 Then
For q = 2 To p
Worksheets(q).Delete
Next q
End If

This however gives me a 'Subscript out of range' if there are more than 2
sheets in the workbook The Debugger tells me that there is something wrong
with:

Worksheets(q).Delete

Hope this is enough information.
Thanks in advance for any help.

KJ

Hi guys - having a blank here. Not enough sleep. I would like a formula to remove all text in a string after the first and only dash.

Thanks for your help

Dear All

I have a group of cells with lots of words and answers in them from an online survey and i would like to delete all of the words execpt for the one from which i would like to sort by.

For example survey results (each line is a new cell not row)

Lake Buffalo Lake Eildon Lake Eildon|Lake Hume|Lake Nillahcootie Lake Eildon|Lake Eppalock|Goulburn Weir (Lake Nagambie)|Lake Hume|Lake Mulwala|Waranga Basin Cain Curran Reservoir|Lake Eildon|Lake Eppalock|Goulburn Weir (Lake Nagambie)|Lake Hume|Waranga Basin Lake Buffalo|Cain Curran Reservoir|Lake Eildon|Lake Eppalock|Goulburn Weir (Lake Nagambie)|Lake William Hovell|Lake Hume|Lake Mulwala|Lake Nillahcootie|Waranga Basin Waranga Basin Lake Buffalo|Cain Curran Reservoir|Lake Eildon|Lake Eppalock|Goulburn Weir (Lake Nagambie)|Waranga Basin Lake Buffalo|Cain Curran Reservoir|Lake Eildon|Lake Eppalock|Goulburn Weir (Lake Nagambie)|Lake Hume|Lake Mulwala|Lake Nillahcootie|Waranga Basin
Lake Buffalo|Cain Curran Reservoir|Lake Eildon|Lake Eppalock|Goulburn Weir (Lake Nagambie)|Lake William Hovell|Lake Hume|Lake Mulwala|Lake Nillahcootie|Waranga Basin Lake Eppalock Cain Curran Reservoir|Lake Eildon|Lake Eppalock|Lake Nillahcootie

I would like to delete everything execpt for the word "Eildon", leaving only Eildon in the cells that have this word, and the ones that dont blank.

Thanks

Andrew

Hello.

I am trying to write a macro which will delete all rows in a worksheet except the header. I have a workbook with 30 sheets and I wanted to have it call each worksheet name and then do the same for each so that I can clear out old data for the new weeks report.

Thanks in advance for your help!

Hello all -

I need some help making a macro for a 2003 version excel file. I have a column (AX) with some entries like the following

2051CG2A22A2AS1B4M5P2
2051TG4A2B21AS1B4M5D4
2088G2S22A1B4
3051S2CD2A2D11A1AM5

The macro needs to find the combination's like 2051 with S1, or 3051S with D11 within the cell. If it finds a combination like that, it should delete the entire row. If I have a macro like that, the only remaining entry from above would be 2088G2S22A1B4. Could somebody please help me out with this....it would be greatly appreciated!!!

Dan

I want to use a macro to delete out all empty rows in a spreadsheet. There
are 1,700 rows and 10 columns. If all of the cells are blank in any of the
rows, I want to delete that row. I am fairly new with VBA.

Hi,

OK I want to delete cells in a spreadsheet based on the contents of Column B

I am gonna word it out so you can understand:

I want to delete all rows in a spreadsheet that contain no data in column B, as long as the data in column A is not "Cookies", or "Salt". The range of cells I want to delete spams from "A7:G7". when deleting this I want to shift the cells up because there is data beyond column "G" that I want to maintain.

After the cells are deleted I will have to restore my formula(s) down until "B200:I200"

Also there is a conditional formatting rule to shade every other row that always gets messed up, I would like it restored to cover the Range "A7:I200" (Note Columns H and I are hidden) The formula I am using is " =MOD(ROW(),2)=1"

Then I will reset the print area.

My original post started here. But I cannot get it to work. Fresh ideas will be more than welcome.

Here is my sample Delete Empty rows (3).xls

Thanks a lot

Hello,

I already have the beginning of the code that i need (see below). I would like to make it so that this code becomes a variable range. The variable should be based on the value of the first column (A) in the sheet.

The reason for this is that is takes quite a bit of effort for excel to convert the columns to numbers, so i would like to make it as short as possible (K2:V500 is often too long). The amount of rows will differ from time to time so i can't just make the predetermened range shorter.

I need to turn the columns K:V to numbers in my sheet. I only need to do this for all the columns where the first column has text in the cell.

I have only been able to make this code work with a predetermened range. Can anyone help me make this variable? Many thanks in advance!


	VB:
	
 Adm9numbers10() 
    Sheets("sheet1").Range("K2:V500").Select 
    Set Rng = Selection 
    For Each c In Rng 
        With Rng 
            c.Value = c.Value + 0 
        End With 
    Next c 
End Sub 

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



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