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
"carlo" wrote:
> On Nov 24, 12:03 pm, me <m...@discussions.microsoft.com> wrote:
> > 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" wrote:
> > > On Nov 22, 1:59 pm, me <m...@discussions.microsoft.com> wrote:
> > > > 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" wrote:
> > > > > Hi me
> >
> > > > > this should work:
> >
> > > > > sub content_del()
> > > > > Range("A" & Cells(65536, 1).End(xlUp).Row + 1 &
> > > > > ":A65536").EntireRow.Delete
> > > > > End Sub
> >
> > > > > hth
> >
> > > > > Carlo
> >
> > > > > On Nov 22, 1:09 pm, me <m...@discussions.microsoft.com> wrote:
> > > > > > 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- Hide quoted text -
> >
> > > > - 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- Hide quoted text -
> >
> > - 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
>
|
Similar Threads
