Weird Numbering Notation

[ShieBoon]

Hi all,

I am using excel 2003. I'm currently stuck at this problem. Situation is, i'm working to improve an excel database right now. When users use the file, they click on buttons and the buttons lead them to a filtered view of the database.

Previously, the code of the button goes like this in general:

Sheets("Main") = User interface where user clicks on buttons
Sheets("MasterList") = Database

Sheets("MasterList").Visible = True
Sheets("Main").Visible = False
Sheets("MasterList").Select
Rows("2:1000").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Country"
Selection.AutoFilter Field:=17, Criteria1:="Address"
Selection.Sort Key1:=Range("P3"), Order1:=xlDescending, Key2:=Range("B3"), Order2:=xlAscending, Key3:=Range("C3"), Order3:=xlAscending, Header:= xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= xlSortNormal
Columns("S").Select
Selection.EntireColumn.Hidden = True
Columns("U:Z").Select
Selection.EntireColumn.Hidden = True
Columns("AC:AZ").Select
Selection.EntireColumn.Hidden = True
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("A2").Select

So users would go ahead to add, delete, update whatever they want to. Users who want to add new records would click on a 'Add New Record' button. The button will then lead the users to a blank page.

Now here's where the problem comes. When users add new records by clicking on the 'Add New Record' button, the records get updated neatly. (If the last record was Row 3, the inserted record will be at Row 4)

However, some users are lazy to do so, and just insert a new record without clicking on the 'Add New Record' button. Thus their record's number starts with Row number 1000.

Attempting to solve that problem, i changed the codes of the buttons in the user interface to the following:

Sheets("MasterList").Visible = True
Sheets("Main").Visible = False
Sheets("MasterList").Select
TotalRow = Rows.Count
Rows("2:" & TotalRow).Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Acc Opening"
Selection.AutoFilter Field:=17, Criteria1:="Completed"
Selection.Sort Key1:=Range("P3"), Order1:=xlDescending, Key2:=Range("B3") , Order2:=xlAscending, Key3:=Range("C3"), Order3:=xlAscending, Header:= xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= xlSortNormal
Columns("S").Select
Selection.EntireColumn.Hidden = True
Columns("U:Z").Select
Selection.EntireColumn.Hidden = True
Columns("AC:AZ").Select
Selection.EntireColumn.Hidden = True
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("A2").Select

It seemed to work fine at first. After the last row of records would be grey space. (Just like after row 65536) But then, i had a new requirement for the system, i'm tasked to allow users to view the records in a 'Form' mode. Thus, i'm now trying to insert new records via the default excel form mode. But after i do so, the records appear at row number 1000+ again instead of the row right after the last record! Even after i changed the codes! I really do not know what is happening. Please advice me.

Thank you!

 

[pbornemeier]

Before using Autofilter in VBA, I always use ActiveSheet.AutoFilterMode = False this ensures that any previously applied filters are removed

I would add a sort routine after the rows are inserted. You may also have to delete any blank lines that sort to the top of the list.

 

[ShieBoon]

Hi Phil, thank you for your time in replying me.

I will try to use 'ActiveSheet.AutoFilterMode = False' right now. Anyway, what do you mean by using a sort routine after the rows are inserted?

Thanks

 

[pbornemeier]

Initially, I thought a sort would be the way to go, if in your database, there is a date/timestamp column? Or a serial number? If a row was inserted after row 1000 and you sorted by a sequential column the data in the 1000+ row would be sorted to the end of the existing data.

If column A was the sequential one then:

    Columns("A:A").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

would work.

But this code will remove all rows with no data in column A and maintain the current order without needing to sort:

Sub RemoveRowsWithColumnABlank()

    Dim lColumnACountA As Long
    Dim lColumnALastDataRow As Long
    
    ActiveSheet.AutoFilterMode = False
    lColumnACountA = WorksheetFunction.Subtotal(3, Columns(1)) 'Counts cells with data
    lColumnALastDataRow = Cells(Rows.Count, 1).End(xlUp).Row  'Finds last row
    
    If lColumnALastDataRow <> lColumnACountA Then

        With Range("A1:A" & lColumnALastDataRow) 'Include header in Autofilter
            .AutoFilter
            .AutoFilter Field:=1, Criteria1:="="
        End With
        With Range("A2:A" & lColumnALastDataRow) 'Dont' delete header
            .SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End With
        ActiveSheet.AutoFilterMode = False
    End If
End Sub

 

[ShieBoon]

Hi Phil, i solved the problem already but nevertheless, thank you for your replies. What i did was to select my last row of record all the way to row 1000 and delete the cells. Apparently that helped to clear the autofilter range, and then i created a new autofilter range from the first row of data to the last row of data. So after that, there aren't any more data inconsistencies. Anyway i got a new problem. Haha. Would you help?

My users would insert new records every now and then. Columns of the worksheet have data validations. (Data -> Validation) I provide a data source for each of these columns. Users insert their input by selecting an option through a 'combobox' within every cell in a row, instead of typing the value. This is for convenience and making sure the user enters the correct input.

Problem is, some columns do not have data sources. But, the input text is normally very long. Something like "Macdonalds Tools Manufacturing Pte Ltd". Thus the users have to type in manually. I want to make it more user-friendly by detecting what the users type and maybe give a suggestion. Like for example, after a user types 'Mac', the rest of the name would appear. The data source of the suggestions would be the column itself.

So now, i'm trying to capture the text characters type in by the user. But under the worksheet events i can only find Worksheet_Change.

Thanks!

 

[pbornemeier]

If Macdonalds Tools Manufacturing Pte Ltd already exists in the same column (and there are no blank rows in that column), as you start typing Mac, and if there are no different entries that start with Mac, the full name will appear and pressing Enter will place that in the row.

You could use the worksheet Selection_Change event to open a userform containing a combobox when the user clicks in the appropriate column.

This post talks about using a dynamic named range as a source for a listbox/combobox:
http://www.mrexcel.com/forum/showthread.php?t=210534

You can see all of the worksheet events by opening a worksheet code page and selecting "Worksheet" instead of "(General)" in the combobox in the VB editor. The various Events are in the combobox to the right of that one.

 

[ShieBoon]

I see, but there are blanks in the row. And i can't fill them up without the users permission. Haha. What do i do now? Are there any formulas that allow me to filter the unique values?

 

[pbornemeier]

If you want to stick with formulas, this thread (Post #10 & #17) describes a method to extract unique values from one column to another. You could then use a dynamic named range comprising the extracted column as the input to your listbox.RowSource:

From http://www.mrexcel.com/forum/showthread.php?t=351789

This is a summary from that thread:

'Full List in Column A; Uniques in column B; no column heads
'Create two named ranges
'Range_List
'  =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
'Count_Uniques
'  =SUM(IF(FREQUENCY(IF(Range_List<>"",MATCH("~"&Range_List,Range_List&"",0)),ROW(Range_List)-ROW(Sheet1!$A$1)+1),1))
'Formula in B1 (confirmed with CSE) & copied down as far as necessary
'  =IF(ROWS($B$1:B1)<=Count_Uniques,INDEX(Range_List,SMALL(IF(FREQUENCY(IF(Range_List<>"",MATCH("~"&Range_List&"",Range_List&"",0)),ROW(Range_List)-ROW($A$1)+1),ROW(Range_List)-ROW($A$1)+1),ROWS($B$1:B1))),"")

 

[ShieBoon]

Okay Phil, i'll try it out. Thanks alot!