Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to delete rows with varying amounts of data
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to delete rows with varying amounts of data
do yo have a Header Row (Row 1) or some other row? If you have a header row in row 1 'check if cell K2 is empty if range("K2") < "" then FirstRow = Range("K2").end(xldown).Row LastrEmpty = FirstRow -1 rows("2:" & LastEmpty).delete end if If you don't have aheader row 'check if cell K1 is empty if range("K1") < "" then FirstRow = Range("K1").end(xldown).Row LastrEmpty = FirstRow -1 rows("1:" & LastEmpty).delete end if -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=195160 http://www.thecodecage.com/forumz |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to delete rows with varying amounts of data
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to delete rows with varying amounts of data
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting rows of data of varying lengths | Excel Discussion (Misc queries) | |||
getting data from an array of varying amounts of comboboxes | Excel Programming | |||
Insert totals after varying rows of data | Excel Programming | |||
Creating Pivot tables by VBA for varying amounts of data | Excel Programming | |||
Remove varying amounts of space characters | Excel Discussion (Misc queries) |