I need to find the first data occurance in column D and delete all rows above
it except rows 1 and 2.

Try

Sub marine()
lastrow = Range("A3").End(xlDown).Row
Range("A3:A" & lastrow - 1).EntireRow.ClearContents
End Sub

Mike

"jmr4h8" wrote:

I need to find the first data occurance in column D and delete all rows above
it except rows 1 and 2.

I may have misreda your post if you reall mean delete the rows as opposed to
clear the contents then use this instead

Sub marine()
lastrow = Range("A3").End(xlDown).Row
Range("A3:A" & lastrow - 1).EntireRow.Delete
End Sub

Mike

"Mike H" wrote:

Try

Sub marine()
lastrow = Range("A3").End(xlDown).Row
Range("A3:A" & lastrow - 1).EntireRow.ClearContents
End Sub

Mike

"jmr4h8" wrote:

I need to find the first data occurance in column D and delete all rows above
it except rows 1 and 2.

This deletes all data in my sheet except for the first 2 rows.

the set up is this... the first 2 rows contain headers.... then there is a
gap of 40 or so rows before the data begins. so rows 3 through 44 are
empty... rows 45 through 233 contain data... the trick is that the rows that
i say are empty are not truely empty, but contain link formulas that return
"" ...nothing... depending on the conditions. What I need is rid the gap so
that what is in row 45 is now in row 3 and row 46 is in row 4 and so on. im
not sure if its possible what do you think?

"Mike H" wrote:

Try

Sub marine()
lastrow = Range("A3").End(xlDown).Row
Range("A3:A" & lastrow - 1).EntireRow.ClearContents
End Sub

Mike

"jmr4h8" wrote:

I need to find the first data occurance in column D and delete all rows above
it except rows 1 and 2.

I knew what you meant and replaced clearcontents with delete... im familiar
with vb but have never worked with inside of excel

"Mike H" wrote:

I may have misreda your post if you reall mean delete the rows as opposed to
clear the contents then use this instead

Sub marine()
lastrow = Range("A3").End(xlDown).Row
Range("A3:A" & lastrow - 1).EntireRow.Delete
End Sub

Mike

"Mike H" wrote:

Try

Sub marine()
lastrow = Range("A3").End(xlDown).Row
Range("A3:A" & lastrow - 1).EntireRow.ClearContents
End Sub

Mike

"jmr4h8" wrote:

I need to find the first data occurance in column D and delete all rows above
it except rows 1 and 2.

empty... rows 45 through 233 contain data... the trick is that the rows that
i say are empty are not truely empty, but contain link formulas that return
"" ...nothing...

That's not a trick it's completely different to what you said first time :)
Try this

Sub marine()
Dim myrange as range, myrange1 As Range
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set myrange = Range("A3:A" & lastrow)
For Each c In myrange
If c.Value = "" Then
If myrange1 Is Nothing Then
Set myrange1 = c.EntireRow
Else
Set myrange1 = Union(myrange1, c.EntireRow)
End If
End If
Next
myrange1.EntireRow.Delete
End Sub


Mike


"jmr4h8" wrote:

This deletes all data in my sheet except for the first 2 rows.

the set up is this... the first 2 rows contain headers.... then there is a
gap of 40 or so rows before the data begins. so rows 3 through 44 are
empty... rows 45 through 233 contain data... the trick is that the rows that
i say are empty are not truely empty, but contain link formulas that return
"" ...nothing... depending on the conditions. What I need is rid the gap so
that what is in row 45 is now in row 3 and row 46 is in row 4 and so on. im
not sure if its possible what do you think?

"Mike H" wrote:

Try

Sub marine()
lastrow = Range("A3").End(xlDown).Row
Range("A3:A" & lastrow - 1).EntireRow.ClearContents
End Sub

Mike

"jmr4h8" wrote:

I need to find the first data occurance in column D and delete all rows above
it except rows 1 and 2.

This also deletes everything except the first two rows. Could it be easier to
hide rows rather than delete them? And to make sure im going about this the
right way...Im right click my sheet tab...then selecting view code and then
pasting the code and finally running it... is that right?

"Mike H" wrote:

empty... rows 45 through 233 contain data... the trick is that the rows that
i say are empty are not truely empty, but contain link formulas that return
"" ...nothing...

That's not a trick it's completely different to what you said first time :)
Try this

Sub marine()
Dim myrange as range, myrange1 As Range
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set myrange = Range("A3:A" & lastrow)
For Each c In myrange
If c.Value = "" Then
If myrange1 Is Nothing Then
Set myrange1 = c.EntireRow
Else
Set myrange1 = Union(myrange1, c.EntireRow)
End If
End If
Next
myrange1.EntireRow.Delete
End Sub


Mike


"jmr4h8" wrote:

This deletes all data in my sheet except for the first 2 rows.

the set up is this... the first 2 rows contain headers.... then there is a
gap of 40 or so rows before the data begins. so rows 3 through 44 are
empty... rows 45 through 233 contain data... the trick is that the rows that
i say are empty are not truely empty, but contain link formulas that return
"" ...nothing... depending on the conditions. What I need is rid the gap so
that what is in row 45 is now in row 3 and row 46 is in row 4 and so on. im
not sure if its possible what do you think?

"Mike H" wrote:

Try

Sub marine()
lastrow = Range("A3").End(xlDown).Row
Range("A3:A" & lastrow - 1).EntireRow.ClearContents
End Sub

Mike

"jmr4h8" wrote:

I need to find the first data occurance in column D and delete all rows above
it except rows 1 and 2.

Hi,

That's odd. What the macro does is take the used range of column A and if a
cell in A is "" the entire row is deleted.

Looking back to your original post you did say column D but given your
understanding of VB I guess you spotted that. maybe hiding is the best option.

Mike

"jmr4h8" wrote:

This also deletes everything except the first two rows. Could it be easier to
hide rows rather than delete them? And to make sure im going about this the
right way...Im right click my sheet tab...then selecting view code and then
pasting the code and finally running it... is that right?

"Mike H" wrote:

empty... rows 45 through 233 contain data... the trick is that the rows that
i say are empty are not truely empty, but contain link formulas that return
"" ...nothing...

That's not a trick it's completely different to what you said first time :)
Try this

Sub marine()
Dim myrange as range, myrange1 As Range
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set myrange = Range("A3:A" & lastrow)
For Each c In myrange
If c.Value = "" Then
If myrange1 Is Nothing Then
Set myrange1 = c.EntireRow
Else
Set myrange1 = Union(myrange1, c.EntireRow)
End If
End If
Next
myrange1.EntireRow.Delete
End Sub


Mike


"jmr4h8" wrote:

This deletes all data in my sheet except for the first 2 rows.

the set up is this... the first 2 rows contain headers.... then there is a
gap of 40 or so rows before the data begins. so rows 3 through 44 are
empty... rows 45 through 233 contain data... the trick is that the rows that
i say are empty are not truely empty, but contain link formulas that return
"" ...nothing... depending on the conditions. What I need is rid the gap so
that what is in row 45 is now in row 3 and row 46 is in row 4 and so on. im
not sure if its possible what do you think?

"Mike H" wrote:

Try

Sub marine()
lastrow = Range("A3").End(xlDown).Row
Range("A3:A" & lastrow - 1).EntireRow.ClearContents
End Sub

Mike

"jmr4h8" wrote:

I need to find the first data occurance in column D and delete all rows above
it except rows 1 and 2.

well funny that you mention replacing A with D... i didnt... but it works
great now that I have. The Data in my column A is usually empty, and was in
this case, hence the macro was working properly by deleting everything... is
there a short cut to rerun the macro without having to use a button in excel
or switching back to vb. ex. like F5 for refresh or something like that...
this workbook is used by people other than myself... most of which have very
little knowledge about excel let alone vb.

Also what modifications would be necesary if i wanted to hide the rows
rather than delete them. I have the feeling that once i delete the row my
link formulas will be gone as well?

Thanks
Justin

"Mike H" wrote:

Hi,

That's odd. What the macro does is take the used range of column A and if a
cell in A is "" the entire row is deleted.

Looking back to your original post you did say column D but given your
understanding of VB I guess you spotted that. maybe hiding is the best option.

Mike

"jmr4h8" wrote:

This also deletes everything except the first two rows. Could it be easier to
hide rows rather than delete them? And to make sure im going about this the
right way...Im right click my sheet tab...then selecting view code and then
pasting the code and finally running it... is that right?

"Mike H" wrote:

empty... rows 45 through 233 contain data... the trick is that the rows that
i say are empty are not truely empty, but contain link formulas that return
"" ...nothing...

That's not a trick it's completely different to what you said first time :)
Try this

Sub marine()
Dim myrange as range, myrange1 As Range
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set myrange = Range("A3:A" & lastrow)
For Each c In myrange
If c.Value = "" Then
If myrange1 Is Nothing Then
Set myrange1 = c.EntireRow
Else
Set myrange1 = Union(myrange1, c.EntireRow)
End If
End If
Next
myrange1.EntireRow.Delete
End Sub


Mike


"jmr4h8" wrote:

This deletes all data in my sheet except for the first 2 rows.

the set up is this... the first 2 rows contain headers.... then there is a
gap of 40 or so rows before the data begins. so rows 3 through 44 are
empty... rows 45 through 233 contain data... the trick is that the rows that
i say are empty are not truely empty, but contain link formulas that return
"" ...nothing... depending on the conditions. What I need is rid the gap so
that what is in row 45 is now in row 3 and row 46 is in row 4 and so on. im
not sure if its possible what do you think?

"Mike H" wrote:

Try

Sub marine()
lastrow = Range("A3").End(xlDown).Row
Range("A3:A" & lastrow - 1).EntireRow.ClearContents
End Sub

Mike

"jmr4h8" wrote:

I need to find the first data occurance in column D and delete all rows above
it except rows 1 and 2.

Alt + F11 will toggle you in and out of VBEditor window.


Gord Dibben MS Excel MVP

On Wed, 2 Jul 2008 12:56:04 -0700, jmr4h8
wrote:

well funny that you mention replacing A with D... i didnt... but it works
great now that I have. The Data in my column A is usually empty, and was in
this case, hence the macro was working properly by deleting everything... is
there a short cut to rerun the macro without having to use a button in excel
or switching back to vb. ex. like F5 for refresh or something like that...
this workbook is used by people other than myself... most of which have very
little knowledge about excel let alone vb.

Also what modifications would be necesary if i wanted to hide the rows
rather than delete them. I have the feeling that once i delete the row my
link formulas will be gone as well?

Thanks
Justin

"Mike H" wrote:

Hi,

That's odd. What the macro does is take the used range of column A and if a
cell in A is "" the entire row is deleted.

Looking back to your original post you did say column D but given your
understanding of VB I guess you spotted that. maybe hiding is the best option.

Mike

"jmr4h8" wrote:

This also deletes everything except the first two rows. Could it be easier to
hide rows rather than delete them? And to make sure im going about this the
right way...Im right click my sheet tab...then selecting view code and then
pasting the code and finally running it... is that right?

"Mike H" wrote:

empty... rows 45 through 233 contain data... the trick is that the rows that
i say are empty are not truely empty, but contain link formulas that return
"" ...nothing...

That's not a trick it's completely different to what you said first time :)
Try this

Sub marine()
Dim myrange as range, myrange1 As Range
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set myrange = Range("A3:A" & lastrow)
For Each c In myrange
If c.Value = "" Then
If myrange1 Is Nothing Then
Set myrange1 = c.EntireRow
Else
Set myrange1 = Union(myrange1, c.EntireRow)
End If
End If
Next
myrange1.EntireRow.Delete
End Sub


Mike


"jmr4h8" wrote:

This deletes all data in my sheet except for the first 2 rows.

the set up is this... the first 2 rows contain headers.... then there is a
gap of 40 or so rows before the data begins. so rows 3 through 44 are
empty... rows 45 through 233 contain data... the trick is that the rows that
i say are empty are not truely empty, but contain link formulas that return
"" ...nothing... depending on the conditions. What I need is rid the gap so
that what is in row 45 is now in row 3 and row 46 is in row 4 and so on. im
not sure if its possible what do you think?

"Mike H" wrote:

Try

Sub marine()
lastrow = Range("A3").End(xlDown).Row
Range("A3:A" & lastrow - 1).EntireRow.ClearContents
End Sub

Mike

"jmr4h8" wrote:

I need to find the first data occurance in column D and delete all rows above
it except rows 1 and 2.