variable rows dataset capture

[mart10]

I am using Excel 2016

I want to run a mcro that pastes data of certain no of columns intoa nother sheet at diferent location (for further imprt into a db) and thats no problem. The issue is the no of rows where data is presnt in the original dataset varies every time. I could go crazy and define a range of say a thousand rows but thats crazt=y and would relult in me importing lots of null rows So I just want to TEST where column A is not null (as coulm A is compulsary in dataset if row of data present)

How do I define that variable dataset? At the moment btw I am also exporting the header row (but that may change)

 

[SkipVought]

Hi,

Sheets(1).Cells(1, 1).CurrentRegion.Copy Sheets(2).Cells(1, 1)

This will copy your table, assuming your table is in the FIRST sheet and starts in A1 and paste it into the second sheet, starting in A1.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[mart10]

I will take a look and see if it works. I think my firt sheet will have headers

|I deally I didnt want to create a second sheet, just paste data rows only into a new file. I did see something about a While loop but didnt make much snnse?

 

[SkipVought]

What kind of new file?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Here's how to copy data only

'
    With ActiveSheet
        Intersect(.Range(.Cells(2, 1), .Cells(2, 1).End(xlDown)).EntireRow, .UsedRange).Copy
    End With

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[mart10]

Its a bit more complex than I have described. The macros creates/transforms data from one type format (Ecel/CSV) into another and I just dont know how or where to insert your code. Im nervous about touching the code as its beyhond me

the user fills in the two sheets shown on the screenshot , then has to highlight manually on each tab whee he sees data in column B and then presses the macro button which then saves transformed selected to a location defined in workbook (for now), its then imported into another system

The macro code looks like this :

Sub create_qid()

Dim Selection As Range
Dim c As Range
Dim heading(1 To 100) As String
Dim tempo As String

Curr_Wsheet = ThisWorkbook.ActiveSheet.Name
QIDPath = Worksheets(Curr_Wsheet).Cells(2, 9) ' Filepath for output QID file

ChDrive ("C:")
ChDir (QIDPath)
tempo = Format(Now, "ddMMMHhNn")
SQLFileName = QIDPath + "\" + Curr_Wsheet + tempo + ".qxt" ' Create the output file .
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile(SQLFileName, True)

Dim record As Integer

record = 0
For nbcol = 1 To 255
t = Len(Cells(4, nbcol).Value)
If t = 0 Then Exit For
record = record + 1
heading(nbcol) = (Cells(4, nbcol).Value)
Next nbcol

Dim info(1 To 100) As String


Worksheets(Curr_Wsheet).Activate


Set Selection = ActiveWindow.RangeSelection
col = Selection.Column
Line = Selection.Row
Nblines = Selection.Rows.Count
a.Writeline ("@" + Cells(1, 2).Value)

For Each c In Selection
If Len(Cells(2, 2).Value) > 0 Then a.Writeline (Cells(2, 2).Value)

For Z = 1 To record
Line = c.Row
info(Z) = Cells(Line, Z).Value

a.Writeline (heading(Z) + info(Z))

Next Z
If Len(Cells(3, 2).Value) > 0 Then a.Writeline (Cells(3, 2).Value)
Next
a.Close

End Sub



and the QID format output looks like this (Ive highlighted the data to do this at moment in column B - but this is the part I want the revised macro to have the inteligence to do on each sheet)


@ProjectedCashFlowDeal
$NEW
TicketNumber=
Instrument=Exposures
DealDate=21/08/2018
ValueDate=30/09/2018
Amount=100
Currency=GBP
Entity=xxx
Counterparty=
AccountNumber=
Bank=NONE
PaymentMethod=
Dealer=
$INSERT
$NEW
TicketNumber=
Instrument=Exposures
DealDate=21/08/2018
ValueDate=30/09/2018
Amount=101
Currency=GBP
Entity=xxx
Counterparty=
AccountNumber=
Bank=NONE
PaymentMethod=
Dealer=
$INSERT


I hope this now makes sense on what I need to do?

 

[SkipVought]

1) a picture is next to worthless
2) there is no Excel/CSV file type. Excel is a spreadsheet application ans CSV (Comma Separates Values) is a TEXT file format.
3) your code has undeclared variables that I had to go through one-by-one and declare
4) can't tell from your PICTURE if there's data in row 4.

So at this point, it's pointless for me to continue to try to GUESS at what your attempting to do.
Please upload your workbook so I can see for certain what' going on.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Here’s what I’d do.

1) convert your table to a Structured Table (Insert > Tables > Table)
2) turn on your macro recorder and record...

[ul]
[li]using the Structured Table filter, select only the rows you want to export.[/li]
[li]turn off the macro recorder.[/li]
[/ul]

3) post your recorded code.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

This is my shot (guess) of what your requirements are (correct me if I am wrong).

You have one worksheet in your Excel workbook.
Header starts in cell A4 and goes: B4, C4, etc. until empty cell in row 4
Data starts in row 5 and can be one row or multiple rows of data
Data can be represented by an empty cell, but column B has to have data - cannot be empty
There are no empty rows between the rows of data, i.e. if you reach empty cell in column B, that’s the end of data

If there are 4 columns with the header and 1 row of data, you want to have a text file in the format of:[tt]
HeaderCell.Value=DataCell.Value
HeaderCell.Value=DataCell.Value
HeaderCell.Value=DataCell.Value
HeaderCell.Value=DataCell.Value
[/tt]
If there are 4 columns with the header and 2 rows of data, you want to have a text file in the format of:
[tt]HeaderCell.Value=DataCell.Value
HeaderCell.Value=DataCell.Value
HeaderCell.Value=DataCell.Value
HeaderCell.Value=DataCell.Value

HeaderCell.Value=DataCell.Value
HeaderCell.Value=DataCell.Value
HeaderCell.Value=DataCell.Value
HeaderCell.Value=DataCell.Value
[/tt]



---- Andy

There is a great need for a sarcasm font.