I need to determine the next cell in the current column that has data and move to that cell.
Thanks,
Will
I need to determine the next cell in the current column that has data and move to that cell.
Thanks,
Will
Re: Next cell with data?
try this...
With Application
'see if the value of the next row in the column is empty
If ActiveCell.Offset(RowOffset:=1).Value = vbNullString Then
'check to make sure we won't jump to the very last row
If ActiveCell.End(xlDown).Row <> ActiveSheet.Rows.Count Then
'jump over the empty cells to the next non empty cell
.Goto ActiveCell.End(xlDown)
End If
Else
'otherwise jump to the next non-empty row in the column
.Goto ActiveCell.Offset(RowOffset:=1)
End If
End With
Display More
Re: Next cell with data?
gijsmo, Thanks for the help. It sort of works in that it will jump to the next row with data in however I only want it to look in the current column, not the other columns. Does that make sense?
Thanks again
Re: Next cell with data?
Let me try to explain a little better. If my cursor is in cell F5 I would like it to jump down column F to the next cell with data in column F, whether it be F6 or all the way down to F100. It doesn't matter which column it just needs to be the active cell column. Does that make more sense? If someone could point me in a direction I will try to work it out.
Thanks in advance.
Re: Next cell with data?
Not sure how you are using the code but that's exactly what the code is designed to do.
The code starts from the current active cell, eg column F row 5 and will then jump down to the next non-empty cell in the column until it gets to the last cell in the column.
Re: Next cell with data?
Quote from gijsmo;764484The code starts from the current active cell, eg column F row 5 and will then jump down to the next non-empty cell in the column until it gets to the last cell in the column.
Yes it does this but if there is nothing in column F until row 20 it stops on row 10 because there is something in A10. I only want it to look in the current cells column.
Re: Next cell with data?
That still seems odd to me. Without seeing the sheet you are trying to run the code on, I have created a sample sheet with the same macro.
forum.ozgrid.com/index.php?attachment/68159/
Just put your cursor in any column with data and use Ctrl+Shift+J to activate the macro...for example it will jump from F5 to F11 even though though there is data in other rows.
This tends to suggest to me that the data you have is not truly "empty". You can test this by manually placing the cursor on F5 in your sheet, then press the End key and then press the Down arrow (simulating what the macro does).
If the cursor moves to F10 instead of F20 as you would expect then F10 is not "empty".
Re: Next cell with data?
Ahhh.....I see said the blind man. My sheet is generated from another program and it is putting in 0's where there "APPEAR" to be blanks. I never realized it did that. My apologies if I ruffled any feathers previously.
Now that you have sorted that out can it be modified to find the next value greater than or less than 0 and jump to it?
Thanks in advance.
Re: Next cell with data?
Can you provide a small sample of the sheet in question so we can see what this data that looks empty (but isn't) looks like.
Re: Next cell with data?
forum.ozgrid.com/index.php?attachment/68160/
Here you go.
Re: Next cell with data?
Based on the sample you sent me, the following should do the trick of jumping to next non-zero row in a column relative to the ActiveCell.
Sub JumpToNextNonZeroCell()
Dim ws1 As Worksheet
Dim lTop As Long, lEnd As Long, lRight As Long
Dim sCol As String
Set ws1 = ThisWorkbook.ActiveSheet
'find the beginning of the next possible row to jump to
'this is simply the row after the ActiveCell
lTop = ActiveCell.Row + 1
With ws1.UsedRange
'find a spot to place a temporary formula
lRight = .Columns.Count + 1
'work out the last row
lEnd = ws1.UsedRange.Rows.Count
'make sure there is a row left to jump to
If lTop > lEnd Then Exit Sub
End With
'determine the column number for the formula
sCol = ColLtr(ActiveCell.Column)
With ws1.Cells(1, lRight)
On Error Resume Next
'use a formula to derive the next non-zero column
.Formula = "=INDEX(MATCH(1,--(" & sCol & lTop & ":" & sCol & lEnd + 1 & "<>0),),)"
'jump to the next non-zero cell based on the value calculated by the formula
Application.Goto ws1.Cells(lTop + .Value - 1, ActiveCell.Column)
On Error GoTo 0
'and remove the formula from the temporary cell
.ClearContents
End With
End Sub
Function ColLtr(ByVal iColNbr As Integer) As String
'this function returns the column letter number from the passed in numeric value
Dim sAdr As String
On Error Resume Next
sAdr = Cells(1, iColNbr).Address
ColLtr = Mid(sAdr, InStr(sAdr, "$") + 1, InStr(2, sAdr, "$") - 2)
On Error GoTo 0
End Function
Display More
Re: Next cell with data?
Quote from gijsmo;764515
It ends after the section in red. lTop is always 1 more than the current row number and lEnd is always 1.
Re: Next cell with data?
I did some digging around and I found this code that will return the last row used.
With Sheets("Sheet1")
If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
lastCol = .Cells.Find(What:="*", _
After:=.Range("a1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Else
lastCol = 1
End If
End With
Display More
However I would not want it only for sheet 1. Just thought this might help you.
Re: Next cell with data?
The code was written using the testdata file you posted. It works fine on my copy with code attached.
forum.ozgrid.com/index.php?attachment/68169/
Not sure how you are invoking the macro to test it.
It is entirely dependent on the current ActiveCell location so, for test purposes, I have assigned the macro to the Ctrl+Shift+J hot key sequence so the cursor is not moved from the ActiveCell inadvertently.
Re: Next cell with data?
WOOHOO! I finally got it to work. I do have one small request though (I think it's small anyway).
I store most of my code in a "Personal" file so any open file can have access to it. This portion of your code "Set ws1 = ThisWorkbook.ActiveSheet" references the file where the code is stored which is empty. Is there an easy way to change "ThisWorkbook" to maybe the active workbook or something?
Other than that it works like a charm!
Thank You! Thank You! Thank You!
Will
Re: Next cell with data?
I was able to replace ThisWorkbook with ActiveWorkbook and it seems to work fine.
I was testing it on some random data though and it was picking some columns. Or it jumped to the first item but not the next item. Some columns it wouldn't jump to any items.
[ATTACH=CONFIG]68170[/ATTACH]
Re: Next cell with data?
Activeworkbook would fix the issue if you're storing it in your personal workbook (or simply Set ws1 = Activesheet).
The formula that determines where to jump to next is designed as per specifications to jump to next non-zero row in the column. It assumed you would start at row 1.
In your random sample of data, your first row is 5 not 1 so the calculation for the end row is not correct.
Change the code from :
lEnd = ws1.UsedRange.Rows.Count
To
lEnd = .Rows.Count + .Row
and it should work for all data even if you don't start at row 1.
NOTE: jumping to next "non-zero" row works on most data including text but may not jump as you expect if there is a "null" in the cell - these appear to be empty but aren't.
Re: Next cell with data?
Works Great! Thank You so much!!!
Re: Next cell with data?
my pleasure...
also, I think that last change should be:
lEnd = .Rows.Count + .Row - 1
otherwise you'll be one row past the last row
Don’t have an account yet? Register yourself now and be a part of our community!