find next used cell after blank cell (1 Viewer)

smiler44 · Sep 24, 2013

I am trying to find the next cell/row in a column that is populated after one or more blank rows/cells in a column.
for instance
A1 to A10 are populated
A11 to A13 are empty
A14 to A20 are populated.

Although the column will be the same the ranges may vary. How do I find that A14 is the next used cell after the empty cells.

I have this but I don't feel secure using it. If cell A1 is selected:

Code:

Sub Macro1()
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    
End Sub

thank you
smiler44

lemo · Sep 30, 2013

i had to do something conceptually similar recently, and after some research and testing decided to go with what i thought was the safest - cell by cell check. it's also the most time consuming, i believe, so judge for yourself if it suits your project.
here is a piece of my code, it looks for the word "Bronx" in column B, then finds how many blanks are below that word before the next one, ect.
i think with minor modification it should suffice.. let us know.

Code:

lastrow = ActiveSheet.Cells(Rows.Count, "G").End(xlUp).Row

For Each c In Range("b12:b" & lastrow)
    If c.Value = "Bronx" Then
        startloc = c.Row
        For r = startloc To lastrow 'to find end of boro.
            If (Range("B" & (r + 1)) <> "" Or r = lastrow) Then endloc = r: Exit For
        Next r
        Range("B" & startloc & ":I" & endloc).Copy

Rx_ · Sep 30, 2013

Not sure about your level of use. This is Excel code that is run from within MS Access.
It is not well documented. I threw it together to go through several thousand Excel worksheets that had years of data and import it into SQL Server via MS Access.
This is just 2 of several dozen procedures. Will try to explain the parts you are asking about.

At code line 120 - the code steps through row-by-row (a loop)
Using the offset, check to see if the cell is blank or if it contains data of a specific type. Sometimes the data just has a note at the end or sometimes a formula for a summary.

Code:

Function GetDatafromTextFile(ByVal strFile As String)
      Dim xlsApp As Excel.Application, xlsWorkBook As Excel.Workbook, xlsSheet As Excel.Worksheet
      Dim DB As Database, rs As DAO.Recordset, strUNITID As String
      Dim gcount As Long
On Error GoTo ErrHandler:

10    Set DB = CurrentDb
20    Set rs = DB.OpenRecordset("Select * from [tblENASummary]", dbOpenDynaset)

30    Set xlsApp = New Excel.Application

40    Set xlsWorkBook = xlsApp.Workbooks.Open(strFile)
50    Set xlsSheet = xlsApp.Worksheets(1)
60    For i = 2 To xlsSheet.Cells(xlsSheet.Rows.count, "A").End(xlUp).ROW Step 1 ' steps through line by line evaluating the files contents
70        gcount = gcount + 1
80        SysCmd acSysCmdSetStatus, gcount
90        If i = 2 Then
100           strUNITID = ExtractedUNITID(xlsSheet.Cells(i, 1)) ' Uses a function to extract out the unit id
110       End If
              ' Locate the start of the data
120       If xlsSheet.Cells(i, 1) > 0 And IsNumeric(xlsSheet.Cells(i, 1)) = True Then
130           If Format(CDate(xlsSheet.Cells(i, 2)), "Short Date") = Format([Forms]![frmMain]![FileDate], "Short Date") Then
140               With rs ' Add new entry
150                   .AddNew
160                   ![UnitID] = strUNITID
170                   ![TransactionID] = xlsSheet.Cells(i, 1)
180                   ![TransactionDate] = CDate(xlsSheet.Cells(i, 2))
190                   ![CCode] = xlsSheet.Cells(i, 3)
200                   ![RejectOrAccept] = xlsSheet.Cells(i, 4)
210                   ![EntryPoint] = xlsSheet.Cells(i, 5)
220                   ![Currency] = xlsSheet.Cells(i, 6)
230                   ![BillType] = xlsSheet.Cells(i, 7)
240                   ![BillQuantity] = xlsSheet.Cells(i, 8)
250                   .Update
260               End With
270           End If
280       End If
290   Next i
          
          
300   rs.Close
310   Set rs = Nothing
320   Set DB = Nothing

330   xlsApp.Quit
340   Set xlsSheet = Nothing
350   Set xlsWorkBook = Nothing
360   Set xlsApp = Nothing
370   Exit Function

ErrHandler:
380   If Err.Number = 1004 Then
          ' The file was not found
390       Err.Clear
400   Else
410       MsgBox Err.Number & " - " & Err.Description
420   End If

430   rs.Close
440   Set rs = Nothing
450   Set DB = Nothing

460   xlsApp.Quit
470   Set xlsSheet = Nothing
480   Set xlsWorkBook = Nothing
490   Set xlsApp = Nothing

End Function

Function ExtractedUNITID(strString As String) As String
Dim strTemp As String, lLocation As Long

lLocation = InStrRev(strString, "\")

strTemp = Left(strString, lLocation - 1)

lLocation = InStrRev(strTemp, "\")

strTemp = Right(strTemp, Len(strTemp) - lLocation)

ExtractedUNITID = strTemp

End Function

Brianwarnock · Oct 1, 2013

smiler44 said:
I am trying to find the next cell/row in a column that is populated after one or more blank rows/cells in a column.
for instance
A1 to A10 are populated
A11 to A13 are empty
A14 to A20 are populated.

Although the column will be the same the ranges may vary. How do I find that A14 is the next used cell after the empty cells.

I have this but I don't feel secure using it. If cell A1 is selected:

Code:

Sub Macro1() Selection.End(xlDown).Select Selection.End(xlDown).Select Selection.End(xlDown).Select End Sub

thank you
smiler44

I don't follow, why are you worried about A1 being selected, surely the initial selection must be in the first block, and when you say you have this it won't give you what you want as it will give the end of the third block after the one the cursor is in.

Have only two lines of code , select A1 and run the macro and the cursor will be at the end of the blanks.

Brian

smiler44 · Oct 1, 2013

Brian it was more I did not feel very secure using it. It works but I just done feel secure, don't trust it.

smiler44

find next used cell after blank cell (1 Viewer)

smiler44

Registered User.

lemo

Registered User.

Rx_

Nothing In Moderation

Brianwarnock

Retired

smiler44

Registered User.

Similar threads

Users who are viewing this thread