| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Finding values in different rows/columns | Excel Worksheet Functions | |||
| Finding the highest value from two columns of data | Excel Worksheet Functions | |||
| Finding unique data between 2 columns | Excel Worksheet Functions | |||
| Finding similar data or numbers in two columns | New Users to Excel | |||
| Finding common data in multiple columns and rows in Excel | Excel Worksheet Functions | |||
Linear Mode
