I'm trying to create a code that will carry out the following.
Check for entries in column 'U' from cell 7 downover until there are no more
entries in column U (i say this so that it does not check all the way to the
bottom of the sheet and take forever, may be wrong though). Then for every
entry in column 'U', check to see if an entry exists in activecell.offset(0,
18). If no entry exists, delete that row.
I have managed to do this by finding the last row and checking if that is
empty and deletes if it is, but it only works once as I dont know how to loop
and it also only checks if the last row is empty.
Could any of you excel wizards help me out or point me in the right direction.
Thanks
Ian

LastRow = Range("U" & Rows.count).end(xlup).row
for RowCount = LastRow to 7 step - 1
if Range("R" & RowCount) = "" then
Row(RowCount).Delete
end if
next RowCount


"Woodi2" wrote:

I'm trying to create a code that will carry out the following.
Check for entries in column 'U' from cell 7 downover until there are no more
entries in column U (i say this so that it does not check all the way to the
bottom of the sheet and take forever, may be wrong though). Then for every
entry in column 'U', check to see if an entry exists in activecell.offset(0,
18). If no entry exists, delete that row.
I have managed to do this by finding the last row and checking if that is
empty and deletes if it is, but it only works once as I dont know how to loop
and it also only checks if the last row is empty.
Could any of you excel wizards help me out or point me in the right direction.
Thanks
Ian

Thanks Joel.

I have tried it as follows

Sub TextBox883_Click()
'
' TextBox883_Click Macro
' Macro recorded 20/04/2009 by BPPassPort User
'
LastRow = Range("U" & Rows.Count).End(xlUp).Row
For RowCount = LastRow To 7 Step -1
If Range("R" & RowCount) = "" Then
Row(RowCount).Delete
End If
Next RowCount

'
End Sub

However I receive the following message
Compile Error:
Sub or function not defined
and it highlights the word Row, just before (RowCount).Delete


"joel" wrote:


LastRow = Range("U" & Rows.count).end(xlup).row
for RowCount = LastRow to 7 step - 1
if Range("R" & RowCount) = "" then
Row(RowCount).Delete
end if
next RowCount


"Woodi2" wrote:

I'm trying to create a code that will carry out the following.
Check for entries in column 'U' from cell 7 downover until there are no more
entries in column U (i say this so that it does not check all the way to the
bottom of the sheet and take forever, may be wrong though). Then for every
entry in column 'U', check to see if an entry exists in activecell.offset(0,
18). If no entry exists, delete that row.
I have managed to do this by finding the last row and checking if that is
empty and deletes if it is, but it only works once as I dont know how to loop
and it also only checks if the last row is empty.
Could any of you excel wizards help me out or point me in the right direction.
Thanks
Ian

The code should be Rows with an s on the end.
Rows(RowCount).Delete

However, I am confused by the code against what you asked for. You said that
you wanted to test the value in Offset(0, 18). Did you mean offset 18 from
column U? If you did then that is column AM.

Joel's code tests column R which is the 18th column on the worksheet. (It is
not even Offset 18 from the first column. It is Offset 17 from the first
column.)

Anyway whatever column you want to test then replace "R" with that column Id.


--
Regards,

OssieMac


"Woodi2" wrote:

Thanks Joel.

I have tried it as follows

Sub TextBox883_Click()
'
' TextBox883_Click Macro
' Macro recorded 20/04/2009 by BPPassPort User
'
LastRow = Range("U" & Rows.Count).End(xlUp).Row
For RowCount = LastRow To 7 Step -1
If Range("R" & RowCount) = "" Then
Row(RowCount).Delete
End If
Next RowCount

'
End Sub

However I receive the following message
Compile Error:
Sub or function not defined
and it highlights the word Row, just before (RowCount).Delete


"joel" wrote:


LastRow = Range("U" & Rows.count).end(xlup).row
for RowCount = LastRow to 7 step - 1
if Range("R" & RowCount) = "" then
Row(RowCount).Delete
end if
next RowCount


"Woodi2" wrote:

I'm trying to create a code that will carry out the following.
Check for entries in column 'U' from cell 7 downover until there are no more
entries in column U (i say this so that it does not check all the way to the
bottom of the sheet and take forever, may be wrong though). Then for every
entry in column 'U', check to see if an entry exists in activecell.offset(0,
18). If no entry exists, delete that row.
I have managed to do this by finding the last row and checking if that is
empty and deletes if it is, but it only works once as I dont know how to loop
and it also only checks if the last row is empty.
Could any of you excel wizards help me out or point me in the right direction.
Thanks
Ian