PDA

View Full Version : Deleting by columns lastrow



khalid79m
02-04-2010, 02:44 AM
I need a code to go from column a to iv , for each column find the lastrow of data then offset 1 row (downwards) and then from that row to 65536 delete the data.

For example column a has data in from rows 1 to 500 , so i need it do delete 501 to 65536 xlshift up then move on the next column.

any help would be appreciated.

GTO
02-04-2010, 02:52 AM
Hi khalid,

Specific to the "lastrow" of data in each column, if there is stuff to delete below this, how are we returning which row has the last data? Are we searching from row 1 downward, and finding the first blank/empty cell, or, are we concerned with 'ghost' data and searching for 'real' data from the bottom row upwards?

Mark

khalid79m
02-04-2010, 03:14 AM
hi gto thanks for the response I copy some data into these cells and the file size increase dramaticly . I manually go thru each cell and find the last visisble data and then delete below it then save and the file size reduces dramatically.

Bob Phillips
02-04-2010, 03:27 AM
By definition, there is NOTHING below the last row, so nothing to delete.

Or are you referring the the last row of a contiguous block of data startsing from row 1?

georgiboy
02-04-2010, 03:34 AM
I think we are looking at dead space here (space bar presses) i used to get them when taking data from old systems at my work, if this is the case you might want to look at the Trim function...

Excuse my code as i have not done this for quite a while now, but it should give you an idea what i mean...
Sub DelData()
Dim MaxCol As Long, LastRow As Long, c As Long, rCell As Range

'last column with data
MaxCol = Cells(1 & Columns.Count).End(xlToLeft).Column

For c = 1 To MaxCol
LastRow = Cells(Rows.Count, c).End(xlUp).Row
For Each rCell In Range(Cells(1, c), Cells(LastRow, c)).Cells
rCell.Value = Trim(rCell.Value)
Next
Next
End Sub

Hope this helps