Deleting content after the last data value in VB or Macro.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

me - 22 Nov 2007 04:09 GMT

I would like to something using macro but really I don't know how. This is
something I like to do
In a data containing worksheet,
1- finding the last cell which has value or text in A1 column in worksheet-A
2- Then delete the rest of the rows thereafter this row number

Something like below. Any help will be appreciated greatly. Thanks

Sub content_del()
$A = Find the last entered value at A1 column
then delete rest of the rows below this number.

Range("???:A1400").Select
Selection.EntireRow.Delete
End Sub

Reply to this Message

carlo - 22 Nov 2007 04:34 GMT

Hi me

this should work:

sub content_del()
Range("A" & Cells(65536, 1).End(xlUp).Row + 1 &
":A65536").EntireRow.Delete
End Sub

hth

Carlo

> I would like to something using macro but really I don't know how. This is
> something I like to do
[quoted text clipped - 11 lines]
> Selection.EntireRow.Delete
> End Sub

Reply to this Message

me - 22 Nov 2007 04:59 GMT

Thank you for you quick reply but somehow it did not worked. I will try to
apply diferently if it can change the result. Thanks

> Hi me
>
[quoted text clipped - 24 lines]
> > Selection.EntireRow.Delete
> > End Sub

Reply to this Message

carlo - 22 Nov 2007 05:37 GMT

> Thank you for you quick reply but somehow it did not worked. I will try to
> apply diferently if it can change the result. Thanks
[quoted text clipped - 29 lines]
>
> - Show quoted text -

did you see, that my post cut the line in half?
this one should actually be on one line:
'----------------------------------------------------------------------------------
Range("A" & Cells(65536, 1).End(xlUp).Row + 1 &
":A65536").EntireRow.Delete
'----------------------------------------------------------------------------------

hth Carlo

Reply to this Message

me - 24 Nov 2007 02:39 GMT

Actually I used it as one line and did not get any syntax error but it did
not work form me. Thank you for your comment though.

> > Thank you for you quick reply but somehow it did not worked. I will try to
> > apply diferently if it can change the result. Thanks
[quoted text clipped - 38 lines]
>
> hth Carlo

Reply to this Message

me - 24 Nov 2007 03:03 GMT

Hi Carlo,

I just realized your code is acctually running fine. I see it delete the
rows after 1400 lines. Between 10th and A1400, altough there is no number or
character, but there are some kind of codes because ethier I use copy and
paste (with macro) or use formula. My goal was to delete those rows so I save
the file as csv, I won't get huge csv file with full of comma.

So now my goal is to find the last cell which has value (text) and delete
remaining rows.

Looking your code, It seems it would be very possible but I really don't
know how. I hope this info is helpfull. Thank you very much.

> > Thank you for you quick reply but somehow it did not worked. I will try to
> > apply diferently if it can change the result. Thanks
[quoted text clipped - 38 lines]
>
> hth Carlo

Reply to this Message

carlo - 26 Nov 2007 00:57 GMT

> Hi Carlo,
>
[quoted text clipped - 54 lines]
>
> - Show quoted text -

Hi

that's weird, it seems, that your column A is not empty, or
empty from row 1400!

my code looks for the last not empty cell in column A and deletes
all rows which lie below that.

hth
Carlo

Reply to this Message

me - 26 Nov 2007 05:24 GMT

Hi Carlo,

Thank you for your help. Finally I was able to find a solution from
discussion board. Somehow little different than initially I discribed. Here
is the solution I used. I intentially placed #### instead of ' "" '. This way
below VB code was able to find them and delete them.

Best Regards

Public Sub delete_blanks()
Dim rCell As Range
Dim rDelete As Range
For Each rCell In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With rCell
If (LCase(.Text) = "####") Then
If rDelete Is Nothing Then
Set rDelete = .Cells
Else
Set rDelete = Union(rDelete, .Cells)
End If
End If
End With
Next rCell
If Not rDelete Is Nothing Then rDelete.EntireRow.Delete
End Sub

> > Hi Carlo,
> >
[quoted text clipped - 65 lines]
> hth
> Carlo

Reply to this Message