Simply Awesome Community here !!!

[slugifur]

Hi Guys n Gals,

I have been a fairly amateurish exceller!! over the years, and due to a recent sales job have I needed to do something a little more complex than sum(a1:a20) etc... !! lol

SO anyway... as I say. I started working for a home improvements company, who were operating in the stoneage wrt technology, and the overwhelming amount of sales brochures, survey pictures, data capture, invoices, commission forms (musnt forget that one) meant that after my first appointment I needed 6 hours on my PC writing the quote up, making sense of my survey notes, putting all the data into a spreadsheet, and then calculating my commission claim to the boss...
sheesh...

so I decided to think about making some user forms so that when I was sitting with the customer asking about conservatory style, ..(roof options, glass/poly, door size, survey info (drains, trees, old structures, soil type, etc etc)floor finish, plastering options, underfloor heating, tiling, laminates, electrical spec... etc etc... )I could easily click a button for each section and spit all the data to a single column, (appropriately indexed with row labels, )

so with the help of info on these boards, I assembled a very admirable selection of userform, with drop downs, picture indexers, flip out extending forms, in-form calculators, etc etc ...And I have to say, that it has taken some effort to piece it all together fixing little bugs wrt identifiers,labels, etc etc... and I have almost got there...
I can now run through slideshows, previous build info etc, style galleries, (in user forms), show all our trade mark reg info, collect all data from the customer, sizes etc etc... its all there.. in about 2 minutes... and my presentations will, I am sure, now come across very professional... and more importantly I have the roots of a database to then use in the future for generating the Customer Quote, and my commission claim etc..

So... whats the question im asking here... ?.

Well.... During several stages of the data collection I have extended list boxes so I that can chose several options... (ie: Edwardian, Glass Roof, Roof Vent) which are just three options from a list of 30 or so.... and it spits this data onto the spreadsheet form that I have labelled "QUOTESTORE"... under the appropriate "PRODUCT" Row...
but... it positions the text based on the selected items column position, with those items not selected blank...
eg: If Edwardian was the third option (lets say Victorian and Lean-To were 1 and 2) and Glass Roof was the 5th Option, (with Polycarbonate as the 4th), and finally "roof vent" is the 12th... with many others between , and after..... then my data appears (under row 14 "Product")
C1 C2 C3 C4 C5 ..........C12
" " " " Edwardian" " " Glass"......."Roof Vent"

which I can easily sort and filter... but... I need to have a button on main_menu user form called "Filter all blanks" (or whatever, which will loop from row 1 through 40 and, taking each row in turn, filter from column 2 (cos column 1 is the row index) to column 32, deleting blanks.... so that only the RELEVENT selected data from each multi-select-list box, is displayed in the row,

sheet name is QUOTESTORE

When I have this data, then I can start looking at a "Quote" form that pulls in each cell with VLOOKUP or something...

I have succesfully muddled through so far, but this one has had me scratching me head for 3 hours now... and im sure that when someone revels (probably 2 lines of code) I wil have a senior Homer Simpson moment.... "DOH !!!"

many thanks guys... you are all legends in helping me get this far...
I have code in my macros that I still struggle with, but it works a treat !! lol

slugifur (Sluggy Fur) after a scruffy old dog we affectionately names SLUGS.. but she moulted like a b***h !!

 

[L. Howard]

So... whats the question im asking here... ?.


which I can easily sort and filter... but... I need to have a button on main_menu user form called "Filter all blanks" (or whatever, which will loop from row 1 through 40 and, taking each row in turn, filter from column 2 (cos column 1 is the row index) to column 32, deleting blanks.... so that only the RELEVENT selected data from each multi-select-list box, is displayed in the row,

sheet name is QUOTESTORE

Yes, what is the question?

I may not be able to offer a solution, but I did read your novel from top to bottom. You might be interested in knowing you missed a close prentices in the paragraph above. See the open one in red.

How about starting here with the sheet named QUOTESTORE and explain clearly and precisely what you want to do about deleting blanks. Just plain speak of what columns, rows and the like.

Regards,
Howard

 

[slugifur]

Job No

<tbody> </tbody>

Date

01/11/2014

Customer Name

JOE SMITH

Address1

120 Letsby Avenue

Address2

Town

Sometown

County

WIltshire

Postode

SN11 2BQ

Phone

77798776876

Email

whatanodd@gmail.com

Description

Joe wants a new 3m x 4m conservatory

Survey

Basic Survey

Existing Structure

Substantial, brikwork, structural, glass

Break-up Base

Break up Conservatory base / concrete

Insulated Base

Full base, as per conservatory

Wall Options

Brick and Brick

Additional height

1

drains

Raise and Seal manhole

Move Rodding Eye

other

Product

Victorian

Glass Roof

K Glass

Box Gutter

Fitting

Basic Installation

Glass Roof

Box Gutter Installation

Tile Hung Property

<tbody>
</tbody>

Hi Howard,

the above is the existing spreadsheet "captured survey data"....
and if you look towards the bottom, you will see the options I selected from my listboxes have been captured but the items not selected have been cptured as blanks..?
so I basically need a filter that loops down through the row numbers with mutliple selected items, and deletes the blanks, so that the record data appears contiguous...
it makes it easier for me to do the next bit of the code...

many thanks
cliff

 

[L. Howard]

I'm thinking you want to start at the word "Options" and delete blank rows down to the bottom of the options data?

Howard

 

[slugifur]

I'm thinking you want to start at the word "Options" and delete blank rows down to the bottom of the options data?

Howard

Hi Howard.. No... I need every row in tact..cos a report will parse the data.... but some of the rows have multiple column data.. This is because the listbox where I clicked several options, say from a list of 20, has then sent all the data to the relevant spreadsheet row... but the "non selected" items have also appeared as blank data... so I need to effectively filter out the blanks so that each row contains the identifier (column1) eg, name or address1, etc....and in column 2 through x the actual data collected... NOT the blanks... so for example :

Fitting:

Basic Installation

Glass Roof

Box Gutter Installation

Tile Hung Property

<tbody>
</tbody>

is saying that for the fitting selection, 4 options were chosen.... and I need it to appear like this:wth blanks removed

Fitting Basic Installation Box Gutter Installation Glass Roof Tile Hung Property <tbody> </tbody>

<tbody>
</tbody>

I can do this with a macro I recorded... but I need it to loop down all the rows.....and check columns 2 to 35 (may expand the range later)

Hope that helps

Cheers

 

[L. Howard]

Give this a try, starts at the last column and increments back to column 2. Check out the msgbox output.

Also looks at the rows and eliminates the blanks there, I have those commented out as I think that would mess up your column formats.

You said you already had a macro and just needed a column looper, so you can delete the commented out lines and the msgbox and paste in your code.

Howard

Option Explicit

Sub ColumnsSearch()
Application.ScreenUpdating = False
    Dim lastColumn As Long
    Dim lastRow As Long

    lastColumn = ActiveSheet.UsedRange.Columns.Count

    Dim i As Long, j As Long
    Dim cell As Range
    For i = lastColumn To 2 Step -1
    MsgBox i
        'lastRow = Cells(Rows.Count, i).End(xlUp).Row
        'For j = lastRow To 1 Step -1
        '    Set cell = Cells(j, i)
         '   If IsEmpty(cell) Then cell.Delete shift:=xlUp
        'Next j
    Next i
    
    Application.ScreenUpdating = True
End Sub

 

[L. Howard]

Buy the way, I would be interested in seeing your code that eliminates blank cells in a row but does not alter the rows above or below the blank in the same column.

You say you recorded such a macro?

Howard

 

[slugifur]

Sub deleteblanks()
'
' deleteblanks Macro
'


'
Range("B18:J18").Select
ActiveWorkbook.Worksheets("QUOTESTORE").sort.SortFields.Clear
ActiveWorkbook.Worksheets("QUOTESTORE").sort.SortFields.Add Key:=Range( _
"B18:J18"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("QUOTESTORE").sort
.SetRange Range("B18:J18")
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
End Sub

this works for each line... but i have 40 macros at the moment ... one for each row...
ive been looking to just have a loop for 1 to 40 that goes down each row and runs the macro...

im going to see if i can get your code integrated and see what happens..

cheers,
cliff

 

[L. Howard]

See if this works for you.

Goes down column B and applies you code to each row column B to J.

Howard

Option Explicit

Sub ForEachRow()
Dim c As Range
Dim Lc As Long, lr As Long
Dim Brng As Range
Dim lColumn As Long

lr = Cells(Rows.Count, 2).End(xlUp).Row
Set Brng = Range("B1:B" & lr)

For Each c In Brng
  'Range("B18:J18").Select
  c.Resize(1, 9).Select
  
 ActiveWorkbook.Worksheets("QUOTESTORE").Sort.SortFields.Clear
 ActiveWorkbook.Worksheets("QUOTESTORE").Sort.SortFields.Add Key:= _
 c.Resize(1, 9), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
 xlSortNormal
 
 With ActiveWorkbook.Worksheets("QUOTESTORE").Sort
 .SetRange c.Resize(1, 9)
 .Header = xlGuess
 .MatchCase = False
 .Orientation = xlLeftToRight
 .SortMethod = xlPinYin
 .Apply
 End With
  
Next

End Sub