LCol = Cells.Find("*", SearchOrder:=xlB圜olumns, SearchDirection:=xlPrevious).ColumnįRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlDown).Row But when you add additional data or change the current values in this range, the order in the range will not be changed automatically. You can use Sort function to sort those values. And you want to sort those values in your data. Public Function GetUsedRange(ws As Worksheet) As Rangeĭim lRow As Integer, lCol As Integer, fCol As Integer, fRow As IntegerįCol = Cells.Find("*", SearchOrder:=xlB圜olumns, SearchDirection:=xlLeft).Column Assuming that you have a list of data in range B1:B5, in which contain sale values. Note: If you want to refine your sorting order, click Add Level and a new sorting line will appear. Choose what order you want to use reorganize your list. Under Column, choose column you want to sort your list. Then, click Add Level in the top left corner and sort by Last Name and choose A to Z. Check the box next to My Data Has Headers. In the window that appears, sort by Household Size and choose Largest to Smallest. Ws.ListObjects(TblName).TableStyle = TblStyle Next, click the Sort & Filter option within the Editing section of the Home tab. Excel is smart enough to recognize that you have data stored in a column behind or next to it. (This will sort the order total from largest to smallest). Now go to Data Table and Sort and click on Z-A to. Ws.ListObjects.Add(xlSrcRange, TblRng,, xlYes).Name = TblName So now, select the column you want to sort last. HOW TO AUTO SORT IN EXCEL WHEN ADDING CODESee -> & Disclaimer This is my blog where I will post updated code Public Sub DatatoTable(ws As Worksheet) You can't convert a "query" range to a table, but you can add a filter. I immediatley ran into trouble adding tables when filters work fine. For example, if you want to re-sort the previous example by delivery date, under Sort by, choose delivery. In the Sort popup window, in the Sort by drop-down, choose the column on which you need to sort. Here is some code to go along w/ George's answer of using a Table. Note: For the best results, each column should have a heading.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |