finding columns that has data by rows
I am going to guess you wanted the dates. Try this on a copy before you run
it on your original.
Sub lime()
Dim lastRow As Long, lastColumn As Long, sh As Worksheet
Set sh = ActiveSheet
lastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
MatchCase:=False).Row
lastColumn = sh.Cells.Find(What:="*", After:=sh.Range("A1"), _
LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, MatchCase:=False).Column
Set srcRng = ActiveSheet.Range("A2:A" & lastRow)
For Each c In srcRng
If c.Value 0 Then
Range("EA" & c.Row) = Range("A1").Value
Else
x = c.End(xlToRight).Column
Range("EA" & c.Row) = Cells(1, x).Value
End If
If Range("DZ" & c.Row).Value 0 Then
Range("EB" & c.Row) = Range("DZ1").Value
Else
y = Range("DZ" & c.Row).End(xlToLeft).Column
Range("EB" & c.Row) = Cells(1, y).Value
End If
Next
End Sub
"DPingger" wrote:
My spreadsheet has 3700 rows with columns A-DZ labeled mm/yyyy in Row1.
Data are numbers 0 per cell.
I need to pull the start (column mm/yyyy) per row where data is 0 and
finish (last cell in a row where data is 0) and enter start in EA and finish
in EB. Need to do it for all 3700 rows.
I've tried index, match and if statements to no avail.
Help please.
TIA
DPingger
|