finding columns that has data by rows

  • Thread starter Thread starter DPingger
  • Start date Start date
D

DPingger

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
 
Do you want the cell values or the cell address for the start and end, or do
you want the dates at the top of the column?
 
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
 
You guessed right, JLGWhiz.

I'll try it now and I'll let you know how it goes.

Thanks.

DPingger
 
Hey Whiz.

It worked on a dummy file last night; worked like a charm on the real file
today.

You're a real whiz, JLG.

Thanks a bunch.

DPingger
 
Happy to help.

DPingger said:
Hey Whiz.

It worked on a dummy file last night; worked like a charm on the real file
today.

You're a real whiz, JLG.

Thanks a bunch.

DPingger
Click to expand...
 
Back
Top