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

[Guest]

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

 

[carlo]

Hi me

this should work:

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


hth

Carlo

 

[Guest]

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

 

[carlo]

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







- 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

 

[me]

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.

 

[me]

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.

 

[carlo]

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.







- 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

 

[me]

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