Hi everyone!

What I want to do is basically
Worksheet.Rows.Delete xlShiftUp
except that I do not want to delete the headings row.

So something like
Worksheet.Rows.Delete (2)
to delete starting from row 2, without having to provide the end of the
range that I am deleting.

Simple question - is there a simple answer?

Thanks!

Lars

i have a simple question to go with yours........ if you don't provide
the end of the range that you are deleting, how in the world will the
macro know where to stop?????

susan


On Jul 21, 11:17*am, Lars Uffmann <a...@nurfuerspam.de> wrote:
> Hi everyone!
>
> What I want to do is basically
> * * Worksheet.Rows.Delete xlShiftUp
> except that I do not want to delete the headings row.
>
> So something like
> * * Worksheet.Rows.Delete (2)
> to delete starting from row 2, without having to provide the end of the
> range that I am deleting.
>
> Simple question - is there a simple answer?
>
> Thanks!
>
> * * Lars

Susan wrote:
> i have a simple question to go with yours........ if you don't provide
> the end of the range that you are deleting, how in the world will the
> macro know where to stop?????

In my particular case, it's supposed to delete all content in the
document, except for the first line. I don't care how it decides where
to stop, I was expecting the same "stop" as in
Worksheet.Rows.Delete xlShiftUp
just with the possibility to exclude the first line(s) from deletion.

Since Excel is apparently able to to that, it should be able to do what
I am trying to do also.

What I have found so far is
Worksheet.Range("2:65535).Delete
Sadly I have to provide a last line here, and I want Excel to be faster
if the document contains less lines. Also, this takes a long time where
Excel just freezes - and I don't understand why it should take longer than
Worksheet.Rows.Delete xlShiftup

Very unsatisfying.


Lars

Lars Uffmann wrote:
> Worksheet.Range("2:65535).Delete

I might also want to add that this method fails in the code, while it
works fine in the direct window during runtime of the function - with
the exact same (copy & paste) code.

in any event, maybe you can amend this coding:
======================
Option Explicit

Sub Lars()


Dim myLastRow As Long
Dim r As Long
Dim c As Range


myLastRow = ActiveSheet.Cells(10000, 1).End(xlUp).Row


For r = myLastRow To 1 Step -1
Set c = ActiveSheet.Range("a" & r)
If c.Value = "" Then
c.EntireRow.Delete
End If
Next r


End Sub
=========================
it looks at column A for blanks and uses the bottom of the range as
being the last populated cell in column A.
if you changed
myLastRow to 1
to
myLastRow to 2
then you'd keep row 1, your header row.
hope it helps.
susan


On Jul 21, 11:17*am, Lars Uffmann <a...@nurfuerspam.de> wrote:
> Hi everyone!
>
> What I want to do is basically
> * * Worksheet.Rows.Delete xlShiftUp
> except that I do not want to delete the headings row.
>
> So something like
> * * Worksheet.Rows.Delete (2)
> to delete starting from row 2, without having to provide the end of the
> range that I am deleting.
>
> Simple question - is there a simple answer?
>
> Thanks!
>
> * * Lars

On Mon, 21 Jul 2008 18:27:27 +0200, Lars Uffmann <(E-Mail Removed)>
wrote:

>Lars Uffmann wrote:
>> Worksheet.Range("2:65535).Delete
>
>I might also want to add that this method fails in the code, while it
>works fine in the direct window during runtime of the function - with
>the exact same (copy & paste) code.

You're missing your second quotemarks in the ().

This worked for me. There's probably a better way of doing it, but at
least it got it done.

x = ActiveSheet.UsedRange.Rows.Count
Rows("2:" & x).Select
Selection.EntireRow.Delete

john
-- freedom is not a concept in which people can do anything they want, be
anything they can be. Freedom is about authority. Freedom is about the
willingness of every single human being to cede to lawful authority a
great deal of discretion about what you do. - Rudy Giuliani

Give this a try...

Sub DeleteAllData()
Dim AddressParts() As String
With Worksheets("Sheet1")
AddressParts = Split(.UsedRange.Address, "$")
.Range("A2:A" & AddressParts(UBound(AddressParts))).EntireRow.Delete
End With
End Sub

Rick


"Lars Uffmann" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi everyone!
>
> What I want to do is basically
> Worksheet.Rows.Delete xlShiftUp
> except that I do not want to delete the headings row.
>
> So something like
> Worksheet.Rows.Delete (2)
> to delete starting from row 2, without having to provide the end of the
> range that I am deleting.
>
> Simple question - is there a simple answer?
>
> Thanks!
>
> Lars

Hi John,

john wrote:
> You're missing your second quotemarks in the ().
Of course - but that was a typo in the posting, not in the original code.


> This worked for me. There's probably a better way of doing it, but at
> least it got it done.
> x = ActiveSheet.UsedRange.Rows.Count

UsedRange is what I want, I guess... at least if I have to submit the
row range. And thank you, I didn't think of using Rows ("a:b") - either
way - the delete statement is still taking ages - I am now experimenting
to just copy all the cells I want to a new worksheet and delete the old
worksheet. Seems faster.

Greetings,

Lars

Rick,

Rick Rothstein (MVP - VB) wrote:
> Give this a try...
>
> Sub DeleteAllData()
> Dim AddressParts() As String
> With Worksheets("Sheet1")
> AddressParts = Split(.UsedRange.Address, "$")
> .Range("A2:A" & AddressParts(UBound(AddressParts))).EntireRow.Delete
> End With
> End Sub

Thank you for the input, but for some reason, nothing that I do seems to
solve my speed problem. I have a sheet with ~64k lines, and was
filtering about 57k of those, then deleting those. Maybe it is the
AutoFilter that does not work well together with Deletion, however I am
very dissapointed in Excel here. I have found it to be *much* faster to
just apply an inverted filter, and copypaste everything to a new
worksheet, then delete the old worksheet.

I never used Excel in the past, now I am reassured in that. It's just
not the proper way to do things. If you want to handle data properly
within the office, use MS Access. As much as I despise Microsoft, I'm
willing to give them that they managed to create a fine DB application
there (as for quick & dirty (or not so dirty) solutions).

I'll just migrate this stupid Excel sheet (handling a data import) to MS
Access as soon as possible. Thanks everyone else for their help also.

Boy am I glad when I am finally through with windows - the newest Linux
editions are really nice look & feel!

Best Regards,

Lars

Hi Susan,

Susan wrote:
> For r = myLastRow To 1 Step -1
That's exactly what I wanted to avoid: A loop over all rows and manually
delete each single row.

Thanks anyways, please see my reply to Rick as to why I consider the
case closed.

Best Regards,

Lars