Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rating: Thread Rating: 5 votes, 5.00 average. Display Modes
Old 09-24-2013, 12:23 PM   #1
smiler44
Newly Registered User
 
Join Date: Jul 2008
Location: UK
Posts: 637
Thanks: 13
Thanked 9 Times in 8 Posts
smiler44 is on a distinguished road
find next used cell after blank cell

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

smiler44 is offline   Reply With Quote
Old 09-30-2013, 11:56 AM   #2
lemo
Newly Registered User
 
Join Date: Apr 2008
Location: nyc
Posts: 185
Thanks: 3
Thanked 3 Times in 3 Posts
lemo is on a distinguished road
Re: find next used cell after blank cell

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
lemo is offline   Reply With Quote
Old 09-30-2013, 12:40 PM   #3
Rx_
Nothing In Moderation
 
Rx_'s Avatar
 
Join Date: Oct 2009
Location: Denver, Colorado
Posts: 2,796
Thanks: 635
Thanked 336 Times in 307 Posts
Rx_ has a spectacular aura about Rx_ has a spectacular aura about Rx_ has a spectacular aura about
Post Re: find next used cell after blank cell

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

__________________
Were you lucky enough to get an answer? Please mark your question as [SOLVED] The original poster can go to Thread Tools to mark it as Solved.

Quotation Thomas Jefferson: "Peace is that brief glorious moment in history when everybody stands around reloading."

There are 2 Kinds of Countries on this Planet
1. Those that use the Metric System
2. Those that had a man walk on the moon

Denver, Colorado - The "Mile High City" - non-metric!
Rx_ is offline   Reply With Quote
Old 10-01-2013, 06:57 AM   #4
Brianwarnock
Retired
 
Brianwarnock's Avatar
 
Join Date: Jun 2003
Location: Merseyside England
Posts: 12,701
Thanks: 39
Thanked 538 Times in 520 Posts
Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light
Re: find next used cell after blank cell

Quote:
Originally Posted by smiler44 View Post
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
__________________
What is this life if, full of care,
We have no time to stand and stare

I do not have Access these days 2015
Brianwarnock is offline   Reply With Quote
Old 10-01-2013, 09:15 AM   #5
smiler44
Newly Registered User
 
Join Date: Jul 2008
Location: UK
Posts: 637
Thanks: 13
Thanked 9 Times in 8 Posts
smiler44 is on a distinguished road
Re: find next used cell after blank cell

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

smiler44 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Find a cell value from range and cell name tim91700 Excel 7 07-29-2013 12:43 AM
VBA to find 1st Blank cell through Access/Excel integration VBA nagiese Modules & VBA 2 09-25-2012 09:14 AM
Get used row range, cell reference, blank cell smiler44 Excel 12 04-30-2009 02:18 AM
Help: Code for changing cell colour depending on cell contents philipwaldram Excel 6 08-26-2008 10:45 AM
Sumif based on cell format or last cell in column qafself Excel 3 01-17-2006 11:06 AM




All times are GMT -8. The time now is 03:46 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World