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?
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 :(
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.
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.
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?
check using:
msgbox typename(sn)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.