End of Sheet
 

Is there any kind of End of Sheet indicator (like an EOF value) With VBA with
Excel? I've got a .csv file that lists a number of purchases. There are
certain rows that I want to remove from the file, some sub total rows and
what not.

The easiest way I can think of to handle that is to cycle through column 'C'
(where the order number is located) and if that column is blank, or has an *
in it, then I know I can delete it. The size of the file will vary depending
on the date range that was used in creating the .csv. So I was wondering if
there was an easy way to figure out where the last row with any data in it
is, or if I need to just use a row number that I know will never be reached
(i.e. 2000)?

Thank you in advance for any help offered.

End of Sheet
 

maybe something like this:

do while not eof

' your code

loop
--


Gary


"RSteph" wrote in message
...
Is there any kind of End of Sheet indicator (like an EOF value) With VBA with
Excel? I've got a .csv file that lists a number of purchases. There are
certain rows that I want to remove from the file, some sub total rows and
what not.

The easiest way I can think of to handle that is to cycle through column 'C'
(where the order number is located) and if that column is blank, or has an *
in it, then I know I can delete it. The size of the file will vary depending
on the date range that was used in creating the .csv. So I was wondering if
there was an easy way to figure out where the last row with any data in it
is, or if I need to just use a row number that I know will never be reached
(i.e. 2000)?

Thank you in advance for any help offered.

End of Sheet
 

LastRow = Cells(Rows.Count,"A").End(xlUp).Row

when deleteing, work bottonm up

For i = LastRoiw to 1 Step -1
If Cells(i,"A").Value = "£" Or _
Cells(i,"A").Value = "*" Then
Rows(i).delete
End If
Next i

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RSteph" wrote in message
...
Is there any kind of End of Sheet indicator (like an EOF value) With VBA
with
Excel? I've got a .csv file that lists a number of purchases. There are
certain rows that I want to remove from the file, some sub total rows and
what not.

The easiest way I can think of to handle that is to cycle through column
'C'
(where the order number is located) and if that column is blank, or has an
*
in it, then I know I can delete it. The size of the file will vary
depending
on the date range that was used in creating the .csv. So I was wondering
if
there was an easy way to figure out where the last row with any data in it
is, or if I need to just use a row number that I know will never be
reached
(i.e. 2000)?

Thank you in advance for any help offered.

End of Sheet
 

That's what I was thinking... but it's been a while since I've done much
Excel, I didn't know if EOF would work or not.

Thank you.

"Gary Keramidas" wrote:

maybe something like this:

do while not eof

' your code

loop
--


Gary


"RSteph" wrote in message
...
Is there any kind of End of Sheet indicator (like an EOF value) With VBA with
Excel? I've got a .csv file that lists a number of purchases. There are
certain rows that I want to remove from the file, some sub total rows and
what not.

The easiest way I can think of to handle that is to cycle through column 'C'
(where the order number is located) and if that column is blank, or has an *
in it, then I know I can delete it. The size of the file will vary depending
on the date range that was used in creating the .csv. So I was wondering if
there was an easy way to figure out where the last row with any data in it
is, or if I need to just use a row number that I know will never be reached
(i.e. 2000)?

Thank you in advance for any help offered.

End of Sheet
 

I tried this but I'm getting an error on the "LastRow = Cells()LastRow =
Cells(Rows.Count,"A").End(xlUp).Row" line.

The error I'm getting is:
"Object variable or With block variable not set."

Here's the code I'm using:

Dim lastRow As Range
Dim i As Integer 'Used to incriment For Loop.

MsgBox ("1")

lastRow = Cells(Rows.Count, "H").End(xlUp).Row 'Get the last row on the page.

MsgBox ("2")

For i = lastRow To 1 Step -1 'Loop through to the end of the page, from
bottom up.
If Cells(i, "C").Value = "" Then
MsgBox ("3")
Rows(i).Delete 'Delete row.
ElseIf Cells(i, "C").Value = "*" Then 'Remove *'s, if now blank. Then
MsgBox ("4")
Rows(i).Delete 'Delete row.
End If

MsgBox ("5")
Next i

The message boxes are to help me with debugging. I get the error thrown just
after MsgBox("1") appears. Am I declaring the variable with the wrong type?


"Bob Phillips" wrote:

LastRow = Cells(Rows.Count,"A").End(xlUp).Row

when deleteing, work bottonm up

For i = LastRoiw to 1 Step -1
If Cells(i,"A").Value = "£" Or _
Cells(i,"A").Value = "*" Then
Rows(i).delete
End If
Next i

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RSteph" wrote in message
...
Is there any kind of End of Sheet indicator (like an EOF value) With VBA
with
Excel? I've got a .csv file that lists a number of purchases. There are
certain rows that I want to remove from the file, some sub total rows and
what not.

The easiest way I can think of to handle that is to cycle through column
'C'
(where the order number is located) and if that column is blank, or has an
*
in it, then I know I can delete it. The size of the file will vary
depending
on the date range that was used in creating the .csv. So I was wondering
if
there was an easy way to figure out where the last row with any data in it
is, or if I need to just use a row number that I know will never be
reached
(i.e. 2000)?

Thank you in advance for any help offered.

End of Sheet
 

I can't see that code that you say errors in the code that you posted.

LastRow should be Dimmed as type Long not Range, it is a row number.

Why do you set LastRow according to the contents of column H, but then test
column C. Shouldn't they be the same?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RSteph" wrote in message
...
I tried this but I'm getting an error on the "LastRow = Cells()LastRow =
Cells(Rows.Count,"A").End(xlUp).Row" line.

The error I'm getting is:
"Object variable or With block variable not set."

Here's the code I'm using:

Dim lastRow As Range
Dim i As Integer 'Used to incriment For Loop.

MsgBox ("1")

lastRow = Cells(Rows.Count, "H").End(xlUp).Row 'Get the last row on the
page.

MsgBox ("2")

For i = lastRow To 1 Step -1 'Loop through to the end of the page, from
bottom up.
If Cells(i, "C").Value = "" Then
MsgBox ("3")
Rows(i).Delete 'Delete row.
ElseIf Cells(i, "C").Value = "*" Then 'Remove *'s, if now blank. Then
MsgBox ("4")
Rows(i).Delete 'Delete row.
End If

MsgBox ("5")
Next i

The message boxes are to help me with debugging. I get the error thrown
just
after MsgBox("1") appears. Am I declaring the variable with the wrong
type?


"Bob Phillips" wrote:

LastRow = Cells(Rows.Count,"A").End(xlUp).Row

when deleteing, work bottonm up

For i = LastRoiw to 1 Step -1
If Cells(i,"A").Value = "£" Or _
Cells(i,"A").Value = "*" Then
Rows(i).delete
End If
Next i

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RSteph" wrote in message
...
Is there any kind of End of Sheet indicator (like an EOF value) With
VBA
with
Excel? I've got a .csv file that lists a number of purchases. There
are
certain rows that I want to remove from the file, some sub total rows
and
what not.

The easiest way I can think of to handle that is to cycle through
column
'C'
(where the order number is located) and if that column is blank, or
has an
*
in it, then I know I can delete it. The size of the file will vary
depending
on the date range that was used in creating the .csv. So I was
wondering
if
there was an easy way to figure out where the last row with any data
in it
is, or if I need to just use a row number that I know will never be
reached
(i.e. 2000)?

Thank you in advance for any help offered.

End of Sheet
 

Changing the Dim on lastRow fixed it.

I'm checking last row according to column H because not every cell in a
given row has content it. In this case column H has cost values, so there is
a value in every cell to the bottom of the page. In column C there's ID#'s.
Some of the rows are blank in this column - those rows that list
total/subtotal values. Those are the rows that I'm trying to get rid of, so
I'm getting the bottom of the page by the costs, and the remove all the
total/subtotal, etc. lines.

Thanks again for your help on running this. I changed lastRow to Long and it
worked perfectly.


"Bob Phillips" wrote:

I can't see that code that you say errors in the code that you posted.

LastRow should be Dimmed as type Long not Range, it is a row number.

Why do you set LastRow according to the contents of column H, but then test
column C. Shouldn't they be the same?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RSteph" wrote in message
...
I tried this but I'm getting an error on the "LastRow = Cells()LastRow =
Cells(Rows.Count,"A").End(xlUp).Row" line.

The error I'm getting is:
"Object variable or With block variable not set."

Here's the code I'm using:

Dim lastRow As Range
Dim i As Integer 'Used to incriment For Loop.

MsgBox ("1")

lastRow = Cells(Rows.Count, "H").End(xlUp).Row 'Get the last row on the
page.

MsgBox ("2")

For i = lastRow To 1 Step -1 'Loop through to the end of the page, from
bottom up.
If Cells(i, "C").Value = "" Then
MsgBox ("3")
Rows(i).Delete 'Delete row.
ElseIf Cells(i, "C").Value = "*" Then 'Remove *'s, if now blank. Then
MsgBox ("4")
Rows(i).Delete 'Delete row.
End If

MsgBox ("5")
Next i

The message boxes are to help me with debugging. I get the error thrown
just
after MsgBox("1") appears. Am I declaring the variable with the wrong
type?


"Bob Phillips" wrote:

LastRow = Cells(Rows.Count,"A").End(xlUp).Row

when deleteing, work bottonm up

For i = LastRoiw to 1 Step -1
If Cells(i,"A").Value = "£" Or _
Cells(i,"A").Value = "*" Then
Rows(i).delete
End If
Next i

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RSteph" wrote in message
...
Is there any kind of End of Sheet indicator (like an EOF value) With
VBA
with
Excel? I've got a .csv file that lists a number of purchases. There
are
certain rows that I want to remove from the file, some sub total rows
and
what not.

The easiest way I can think of to handle that is to cycle through
column
'C'
(where the order number is located) and if that column is blank, or
has an
*
in it, then I know I can delete it. The size of the file will vary
depending
on the date range that was used in creating the .csv. So I was
wondering
if
there was an easy way to figure out where the last row with any data
in it
is, or if I need to just use a row number that I know will never be
reached
(i.e. 2000)?

Thank you in advance for any help offered.

End of Sheet
 

That's good, just checking that it wasn't an oversight.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RSteph" wrote in message
...
Changing the Dim on lastRow fixed it.

I'm checking last row according to column H because not every cell in a
given row has content it. In this case column H has cost values, so there
is
a value in every cell to the bottom of the page. In column C there's
ID#'s.
Some of the rows are blank in this column - those rows that list
total/subtotal values. Those are the rows that I'm trying to get rid of,
so
I'm getting the bottom of the page by the costs, and the remove all the
total/subtotal, etc. lines.

Thanks again for your help on running this. I changed lastRow to Long and
it
worked perfectly.


"Bob Phillips" wrote:

I can't see that code that you say errors in the code that you posted.

LastRow should be Dimmed as type Long not Range, it is a row number.

Why do you set LastRow according to the contents of column H, but then
test
column C. Shouldn't they be the same?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RSteph" wrote in message
...
I tried this but I'm getting an error on the "LastRow = Cells()LastRow
=
Cells(Rows.Count,"A").End(xlUp).Row" line.

The error I'm getting is:
"Object variable or With block variable not set."

Here's the code I'm using:

Dim lastRow As Range
Dim i As Integer 'Used to incriment For Loop.

MsgBox ("1")

lastRow = Cells(Rows.Count, "H").End(xlUp).Row 'Get the last row on
the
page.

MsgBox ("2")

For i = lastRow To 1 Step -1 'Loop through to the end of the page,
from
bottom up.
If Cells(i, "C").Value = "" Then
MsgBox ("3")
Rows(i).Delete 'Delete row.
ElseIf Cells(i, "C").Value = "*" Then 'Remove *'s, if now blank.
Then
MsgBox ("4")
Rows(i).Delete 'Delete row.
End If

MsgBox ("5")
Next i

The message boxes are to help me with debugging. I get the error
thrown
just
after MsgBox("1") appears. Am I declaring the variable with the wrong
type?


"Bob Phillips" wrote:

LastRow = Cells(Rows.Count,"A").End(xlUp).Row

when deleteing, work bottonm up

For i = LastRoiw to 1 Step -1
If Cells(i,"A").Value = "£" Or _
Cells(i,"A").Value = "*" Then
Rows(i).delete
End If
Next i

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RSteph" wrote in message
...
Is there any kind of End of Sheet indicator (like an EOF value)
With
VBA
with
Excel? I've got a .csv file that lists a number of purchases.
There
are
certain rows that I want to remove from the file, some sub total
rows
and
what not.

The easiest way I can think of to handle that is to cycle through
column
'C'
(where the order number is located) and if that column is blank,
or
has an
*
in it, then I know I can delete it. The size of the file will vary
depending
on the date range that was used in creating the .csv. So I was
wondering
if
there was an easy way to figure out where the last row with any
data
in it
is, or if I need to just use a row number that I know will never
be
reached
(i.e. 2000)?

Thank you in advance for any help offered.