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 © 2019 vBulletin Solutions Inc. All rights reserved.