Sub Join() 'David Mc Ritchie 1998-08-05 [Ctrl j] documented in ' cells in selected portion of a row together 'can be used as a reversal of Data/Test2cols or Sep Term() Application. Calculation = xl Calculation Manual On Error Resume Next Dim i Rows as Long, m Row as Long, ir as Long, ic as Long i Rows = Selection. You could, of course, eliminate the dependence with copy, the Edit, paste special, values and then eliminate the original column.

The CODE Worksheet Function determines the ASCII code for a single character. The use of Special Cells prevents truly empty [ISEMPTY in VBA or ISBLANK in Excel] cells from being processed.

The TRIM Worksheet Function will remove code 32 space from left and right sides. CHR(160) is the non breaking space character ( ) in HTML which will be included by changing them first to normal spaces. Other characters you may see are TAB, CR, LF which have decimal values of 09, 13, and 10.

Start with selecting a group of cells such as columns D: F to find out if they are blank, numbers, or text. Screen Updating = True done: End Sub Sub Re Enter As Values() 'D. Dim Rng As Range On Error Go To done Set Rng = Intersect(Selection, Selection.

Only cells within the used range will be identified. -- no loops, preferred solution Dim Rng As Range On Error Go To done Set Rng = Intersect(Selection, Selection. Special Cells(xl Formulas)) If Rng Is Nothing Then Exit Sub On Error Go To 0 Application. Calculation = xl Calculation Manual 'pre XL97 xl Manual Dim cell As Range For Each cell In Rng cell. Value 'to get text values instead use ="'" & cell.value Next cell Application.

Special Cells(xl Constants, xl Numbers)) If cell Revers I() can be used to reverse the order of items in a row, column, or range. Applied to a SINGLE ROW, the macro will flip about a vertical axis, or a horizontal axis for a column; otherwise, it really isn't a flip. If you select a range of columns and rows the item in the upper left will reappear in the lower right corner. Until rewritten arrangement or presence of cells not in selection is undefined. Count If n Rows 256 Then Go To done n Rows = Input Box("Specify number of rows, suggesting " & n Rows, _ "Selection of Number of rows to Rotate", Selection. Count) n Rows = n Rows 1 'adjustment for inserts For i = n Rows To 2 Step -1 Rows(1). A check will be made that no cells contain data in the adjacent column to the right, but you can override this. Row If m Row 0 Then i Answer = Msg Box("Found non-blank in adjacent column -- " _ & Selection. You can remove the dependency by using copy and paste special value. for street numbers (leftmost word): =LEFT(TRIM(CLEAN(SUBSTITUTE(A1, CHAR(160)," "))), SEARCH(" ", TRIM(CLEAN(SUBSTITUTE(A1, CHAR(160)," "))))-1) for street names (remainder): =RIGHT(TRIM(CLEAN(SUBSTITUTE(A1, CHAR(160)," "))), LEN(TRIM(CLEAN(SUBSTITUTE(A1, CHAR(160), " "))))-SEARCH(" ", TRIM(CLEAN(SUBSTITUTE(A1, CHAR(160)," "))))) Finding out what you actually have -- it may not be what it looks like (#debugformat) Formatting: Check what the cell was formatted for with Format, cells and look at the format.

What previously was ordered down will be up, and what ran left to right will run right to left. Sub Revers I() 'David Mc Ritchie 1998-07-30 documented in ' (Flip) Item values in Range, Row, or Column [Ctrl R] 'Counting in multiple rows/cols, item count proceeds down a 'column in range and continues top of next column in range Dim tcells As Long, m Cells As Long, ix As Long, ox As Long Dim i Value As Variant tcells = Selection. Sub Rotate CW() 'Rotate Clockwise: 1) Rotate rows, 2) TRANSPOSE & delete orig 'David Mc Ritchie, 2000-02-07 MISC., documented in ' A1 must be in Selection !!!!! Even after overriding data will not be split if it will remove content from the adjacent column. Suggested shortcut key Sub Sep Term() 'David Mc Ritchie 1998-08-05 [Ctrl t] documented in ' the first term from remainder, as in separating 'street number as first item from street & remainder 'Work on first column, cell to right must appear to be blank '--Application. Changing the format between Text and Number will have no effect on data already entered, but reentry of data will be changed if valid.Good morning, I have the below code that is working fine on windows 32 bit but when I tried it on windows 64 bit I received an error message and I was wondering if you can help me reach a code that works on both 32 and 64 versions Private Sub Auto_Open() Application. Range("M6") = "Submitted for Approval" Then Sheets("Background"). Cell Drag And Drop = False Dim x As String If Sheets("Background"). Only the 5 digit zipcodes are a problem because they get interpreted as a number and get leading zeros stripped.The fix USzip5 subroutine will repair the damage generally introduced by the Text to Data wizard or by software converting a scanned image to an Excel file.Each of the following will change your selection so you will have to start over from: Select cells, Ctrl G (Edit, Go To), [Special] button {and then use one of the following: to find blanks (empty cells), [x] blanks to find text cells, [x] constants, [x] text to find numbers cells, [x] constants, [x] numbers Some worksheet formulas to help show what you actually have: =IF(ISTEXT(E2),"T", IF(ISNUMBER(E2),"N","L"))&LEN(E2) =ISNUMBER(E2) =ISTEXT(E2) =LEN(E2) =ISBLANK(E2) [in VBA the equivalent is ISEMPTY] =CODE(E2) =CODE(RIGHT(E2,1)) =personal.xls! Get Format(E2) ="'*" & D6 & "*" & CODE(RIGHT(D6,1)) Cell View, (40KB installed), Chip Pearson, addin shows the Ascii Code for each character in a cell, which be easier than invoking CODE Worksheet Function for each character yourself (ASC in VBA). Calculation = xl Calculation Automatic Application. Screen Updating = True End Sub and the opposite, to make formulas into text. Tested for single and multiple cell selections with/without formulas Tested for single and multiple cell selections in a sheet without any formulas Following a posting by Ron de Bruin it appears I could have done this without a loop so I will show that version first. Special Cells(xl Formulas)) If Rng Is Nothing Then Exit Sub On Error Go To 0 Application. Calculation = xl Calculation Manual 'pre XL97 xl Manual Dim cell As Range Intersect(Selection, Selection. Calculation = xl Calculation Automatic 'pre XL97 xl Automatic Application. Calculation = xl Calculation Automatic 'pre XL97 xl Automatic Application.

Tags: , ,