Hi,

I have a macro whcih sorts data on column K on one tab then pastes the data
onto a new tab conntaining Lookup formula.

My problem is that the rows pasted contain varying amounts of data and
ideally I would like some code which I incorporate into the current macro
whichi would:
1. locate the first row in Column K which is empty
2. Select columns A:P for this row (whcih DO contain data) and all columns
below this empty column/row (K)
3. Delete all the data in these rows.

Thanks for your help.

Try

Sub Macro()

Dim lngLastRow1 As Long, lngLastRow2 As Long

lngLastRow1 = Cells(Rows.Count, "K").End(xlUp).Row + 1
lngLastRow2 = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, SearchOrder:=xlRows).Row

If lngLastRow2 > lngLastRow1 Then
Rows(lngLastRow1 & ":" & lngLastRow2).ClearContents
End If

End Sub

--
Jacob (MVP - Excel)


"Zakynthos" wrote:

> Hi,
>
> I have a macro whcih sorts data on column K on one tab then pastes the data
> onto a new tab conntaining Lookup formula.
>
> My problem is that the rows pasted contain varying amounts of data and
> ideally I would like some code which I incorporate into the current macro
> whichi would:
> 1. locate the first row in Column K which is empty
> 2. Select columns A:P for this row (whcih DO contain data) and all columns
> below this empty column/row (K)
> 3. Delete all the data in these rows.
>
> Thanks for your help.
>
>

Jacob,

Fantastic! - works perfectly - thank you so much for your help and quick
response.

"Jacob Skaria" wrote:

> Try
>
> Sub Macro()
>
> Dim lngLastRow1 As Long, lngLastRow2 As Long
>
> lngLastRow1 = Cells(Rows.Count, "K").End(xlUp).Row + 1
> lngLastRow2 = ActiveSheet.Cells.Find(What:="*", _
> SearchDirection:=xlPrevious, SearchOrder:=xlRows).Row
>
> If lngLastRow2 > lngLastRow1 Then
> Rows(lngLastRow1 & ":" & lngLastRow2).ClearContents
> End If
>
> End Sub
>
> --
> Jacob (MVP - Excel)
>
>
> "Zakynthos" wrote:
>
> > Hi,
> >
> > I have a macro whcih sorts data on column K on one tab then pastes the data
> > onto a new tab conntaining Lookup formula.
> >
> > My problem is that the rows pasted contain varying amounts of data and
> > ideally I would like some code which I incorporate into the current macro
> > whichi would:
> > 1. locate the first row in Column K which is empty
> > 2. Select columns A:P for this row (whcih DO contain data) and all columns
> > below this empty column/row (K)
> > 3. Delete all the data in these rows.
> >
> > Thanks for your help.
> >
> >