Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display selected rows and columns in a pop-up window.
Hi, I have a spreadsheet "lw1" with 25,000 rows and 21 columns. Most of column C is usually blank. But when column C is not blank, I would like to: 1. Grab columns A, B, C, G, H, J and K from all the rows where column C is not blank. 2. Display these rows in a pop-up window. 3. With the column headings "1st", "2nd", "3rd", "4th", "5th", "6th", "7th". 4. Add a (new) last column to pop-up window called "calculated". 5. Where "calculated" is C+(C*G)+J in each row. 6. As soon as the user clicks on the "OK" button in the pop-up window, I would like to save the contents of the pop-up window into a csv file. Can you give me the VBA code for this so that I can add it to button? Thanks, Luthe ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display selected rows and columns in a pop-up window.
Sub ShowData()
Dim rng As Range, rng1 As Range Dim wkbk As Workbook Dim sh As Worksheet With Worksheets("lw1") Set rng = .Range("A1").CurrentRegion.Resize(, 11) rng.AutoFilter Field:=3, Criteria1:="<" End With Set wkbk = Workbooks.Add(xlWBATWorksheet) Set sh = wkbk.Worksheets(1) rng.Copy sh.Range("A1") sh.Range("D:F,I:I").EntireColumn.Delete sh.Range("A1:G1").Value = Array("1st", "2nd", "3rd", "4th", _ "5th", "6th", "7th") Set rng1 = sh.Range("A1").CurrentRegion Set rng1 = rng1.Offset(1, 0).Resize(rng1.Rows.Count - 1) Load UserForm1 UserForm1.ListBox1.ColumnCount = 7 UserForm1.ListBox1.ColumnHeads = True UserForm1.ListBox1.RowSource = rng1.Address(external:=True) UserForm1.Show wkbk.SaveAs FileName:="C:\Data\Myfile.csv", FileFormat:=xlCSV wkbk.Close SaveChanges:=False End Sub You need to have a userform1 with a listbox named listbox1 and an OK button that unloads the userform. -- Regards, Tom Ogilvy lothario wrote in message ... Hi, I have a spreadsheet "lw1" with 25,000 rows and 21 columns. Most of column C is usually blank. But when column C is not blank, I would like to: 1. Grab columns A, B, C, G, H, J and K from all the rows where column C is not blank. 2. Display these rows in a pop-up window. 3. With the column headings "1st", "2nd", "3rd", "4th", "5th", "6th", "7th". 4. Add a (new) last column to pop-up window called "calculated". 5. Where "calculated" is C+(C*G)+J in each row. 6. As soon as the user clicks on the "OK" button in the pop-up window, I would like to save the contents of the pop-up window into a csv file. Can you give me the VBA code for this so that I can add it to a button? Thanks, Luther ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display selected rows and columns in a pop-up window.
Thanks Tom ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I get excel to display the number of columns selected? | Excel Discussion (Misc queries) | |||
SHADING OF SELECTED ROWS & COLUMNS | Excel Discussion (Misc queries) | |||
How to printout only selected rows and columns | New Users to Excel | |||
Why does AutoFilter display rows NOT selected from drop down box? | Excel Discussion (Misc queries) | |||
Display selected rows from one worksheet to another | Excel Worksheet Functions |