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 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 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

Give this a try...

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

Rick


"Lars Uffmann" wrote in message
...
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.Dele te
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

you're probably trying to use the wrong program to do what you want. just
because excel doesn't work for you in this case, it's probably more suited to a
database app than an excel app. people use the wrong programs to try to solve
problems all of the time.

that's my opinion, anyway.
--


Gary


"Lars Uffmann" wrote in message
...
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.Dele te
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

Gary Keramidas wrote:
you're probably trying to use the wrong program to do what you want.

Let's say my predecessor used the wrong program, and I am reworking
through his crappy coding skills on a production system, so I need the
intermediate step of having a reworked Excel-Sheet work, before
migrating to Access.


just because excel doesn't work for you in this case,
.... doesn't mean Excel is any less crappy *g*

it's probably more suited to a database app than an excel app.
Excel is really just a "database application light". What is your
understanding of an Excel application? However, I don't see how a simple
deletion of a line range is too much to ask of Excel VBA - we're
speaking about Excel freezing for 20-40 seconds here, for a mere 60k
lines. That's ridiculous.

people use the wrong programs to try to solve problems all of the time.
As I said - not my choice, I might eventually migrate it to Access.
For now I've found a workaround.

Best Regards,

Lars

it's probably more suited to a database app than an excel app.

Excel is really just a "database application light". What is your
understanding of an Excel application? However, I don't see how a simple
deletion of a line range is too much to ask of Excel VBA - we're speaking
about Excel freezing for 20-40 seconds here, for a mere 60k lines. That's
ridiculous.

Yes, I guess you could say Excel is a "database application light", but it
also has a monstrous calculating engine attached to it which, I guess, can
get in the way, speed-wise, sometimes. See if this modification to the code
I posted earlier makes things go quicker...

Sub DeleteAllData()
Dim AddressParts() As String
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
With Worksheets("Sheet1")
AddressParts = Split(.UsedRange.Address, "$")
.Range("A2:A" & AddressParts(UBound(AddressParts))).EntireRow.Dele te
End With
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Rick