ExcelBanter - finding columns that has data by rows

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - finding columns that has data by rows (https://www.excelbanter.com/excel-programming/423462-finding-columns-has-data-rows.html)

finding columns that has data by rows

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

finding columns that has data by rows

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?

"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

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

finding columns that has data by rows

You guessed right, JLGWhiz.

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

Thanks.

DPingger

"JLGWhiz" wrote:

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

finding columns that has data by rows

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

"DPingger" wrote:

You guessed right, JLGWhiz.

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

Thanks.

DPingger

"JLGWhiz" wrote:

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

finding columns that has data by rows

Happy to help.

"DPingger" wrote:

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

"DPingger" wrote:

You guessed right, JLGWhiz.

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

Thanks.

DPingger

"JLGWhiz" wrote:

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