PDA

View Full Version : Extract values of a certain format



jblack6572
03-05-2014, 03:05 AM
11365Hi everyone,

First post here.

I'm trying to write some vba to extract only cells that have value of a certain format. For example:

A1
?
A1423
Abe-2167-11
Abe-2099-13
Koz-923-08

I want to copy values who's format matches **-***-** or **-****-**, as in the last three entries. Then paste only those values in column B.

So Column B would now be:

B1
Abe-2167-11
Abe-2099-13
Koz-923-08

Otherwise I can get a list that has the either right format or #Value errors, and I can try extracting only those that don't have the errors:
Sub extractonlyinventions()
Dim rng As Range
Dim cell As Range
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select

Selection.Copy
Set rng = Range("A2, "A2".End(xlDown))
For Each cell In rng
If cell.Text = "#VALUE" Then
cell.Delete
End Sub



Any ideas?

snb
03-05-2014, 03:23 AM
Please post your first sample workbook too.

jblack6572
03-05-2014, 05:59 AM
Updated

ashleyuk1984
03-05-2014, 06:57 AM
This is my very quick take on your problem.



Sub CertainFormat()

Dim LastRow As Integer

Application.ScreenUpdating = False


On Error GoTo Skip:
Columns(1).SpecialCells(2, 16).EntireRow.Delete
Skip:



LastRow = Range("A99999").End(xlUp).Row


For x = 1 To LastRow
If Len(Range("A" & x).Value) = 10 Then
If Mid(Range("A" & x), 4, 1) = "-" And Mid(Range("A" & x), 8, 1) = "-" Then
Range("A" & x).Copy
PasteHere
End If
End If

If Len(Range("A" & x).Value) = 11 Then
If Mid(Range("A" & x), 4, 1) = "-" And Mid(Range("A" & x), 9, 1) = "-" Then
Range("A" & x).Copy
PasteHere
End If
End If
Next x

Application.ScreenUpdating = True


End Sub


Function PasteHere()


If Range("B1").Value = vbNullString Then
Range("B1").PasteSpecial xlPasteValues
Else
Range("B99999").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End If


End Function



Update: Improved my version using part of snb's awesome solution.
snb's solution below is much smaller, and much much faster than my code above!!

Your solutions are very unique snb, but I wish I could understand them better sometimes :(

snb
03-05-2014, 07:41 AM
Please wrap VBA code in code tags !
This code suffices.


Sub M_snb()
Columns(1).SpecialCells(2, 16).EntireRow.Delete

sn = Filter(Filter(Application.Transpose(Columns(1).SpecialCells(2)), "-"), ",", False)
Cells(2, 2).Resize(UBound(sn) + 1) = Application.Transpose(sn)

Columns(2).Sort Cells(1, 2), , , , , , , xlYes
End Sub

jblack6572
03-05-2014, 11:25 AM
Thanks to both! Although I'm really gonna have to think about the second one. I like the conciseness, but I'm not sure I totally understand what's going on.

snb
03-05-2014, 12:32 PM
1. remove all cells in colum A that contain errors (the corresponding row included)

2a. read all cells in column A that contain value in a variable that has 1 'column' and many 'rows'
2b. transpose that array into a 1 dimensional array
2c. filter only those elements of the array that contain a ' - ' in it somewhere
2d. exclude any element that contains a comma
2e. call that array variable 'sn'

3a. make a range with the same amount of elements as the array variable 'sn', starting in cel B2 (=cell(2,2))
3b. since the first element of array variable 'sn' is 0, add one extra cell to match the size of array variable 'sn'
3c. transpose the 1-dimensional array variable 'sn' into a 2-dimensional one so it matches the size of the range it has to be written to

4. sort column B, where cell B1 is meant to be the header of the column, excluded from the sorting.

jblack6572
03-06-2014, 05:54 AM
How would I dimension the sn variable? I've tried it as an array sn() but I keep getting type mismatch.

snb
03-06-2014, 06:57 AM
remove option explicit.

jblack6572
03-06-2014, 07:33 AM
Awesome, that is a quick piece of code.

Just to further my education, what would that variable be dimensioned as if Option Explicit was on?

snb
03-06-2014, 11:06 AM
check using:


msgbox typename(sn)